Tag Archives: Development

Admin

Derived Column vs Script Component

2 Comments

I get asked this from time to time… when should I put a transform inside a Derived Column (DC) and when should I put it in a Script Component (SC).  Well the answer isn’t always easy, but here are a couple guidelines… because you do have to make this decision quite often in SSIS.

Scenario #1:

In short I’d use a DC when the transform is really simple and there’s not much chance of it creeping past what it is initially.  So if it’s going to be something like testing a simple condition and doing a simple replacement based off of it, then a DC is an easy choice to make.  However, if it’s likely that the scope of the transform will shift in the future to something really big then you’re better off with an SC.

Scenario #2:

If the above transform is going to rely on several other columns in the row, then you’re far better off with an SC because it’s much easier to read.  So if you have to compare 4 or 5 cols to get the answer then use an SC.

Scenario #3:

If the transform will be really complex and/or have a lot of sub conditions in it, then it may be possible to do it in a DC, but you’ll be far better off in an SC.  Everything in a DC is on a single line and that model breaks down pretty fast.  And of course there are some sub-conditions that you won’t be able to accurately represent in the very limited DC.

Scenario #4:

If you need to test a data type like IsNumeric(), you can’t do that in a DC at all.  Even though it’s a valid VBA function, SSIS doesn’t support it, so you have to rely on .Net inside of an SC.

Scenario #5:

If you’ve got a lot of simple transforms and you’ve got one that’s more complex or longer, etc, then you may want to consider using an SC simply to keep everything in one place.

That’s all I’ve got.

Admin, Time-savers

Poweshell wins again

1 Comment

It may seem a little redundant, but I love stuff like this. I was asked in front of a group of devs to script out a group of SPs on the prod box and copy them over to the new test box. These SPs stretch across a couple schemas and are named differently from the other ones in those schemas. As it turns out, there are something like 300 of them total. I don’t have a final count.

So when the guy asked me I said sure, that’ll take me like 60secs. And one of the other devs said, there’s no way. You have to check all of those boxes individually and and make sure you don’t miss anything. I said, of course I can. I’m a powershell guy (yes, i actually said that). He was like, even if you could script something like that out, there’s no way to easily get all the ones you need. You’ll be much faster in the wizard.

I told him, I accept your challenge. And for the first time, I gave a dev rights in prod and we had a face-off right there. We sat side by side and both of us started working feverishly to get our SPs scripted. Him in the wizard and me in powershell. Very quickly a crowd gathered. We prob had like 15-20 people gather. These were the PMs, other devs, report writers, etc. They all wanted to see if the bigshot DBA MVP could be taken down by a lowly dev.

Unfortunately like 90secs later, I completed my script and was building my file with my scripted SPs. He was still slugging his way through the wizard and wasn’t even close to having all his little boxes checked. When I finished, I just stood up and walked out as everyone clapped. When I left to come back upstairs he was still working at the wizard determinded to at least finish.

At least that’s how my powershell hero stories always play-out in my mind. I really did get that assignment today, but it was through email and I just did it without any pomp and circumstance. Oh well, a guy can dream can’t he?

Here’s the code I wrote today to pull SPs by schema and matching a pattern. I used the regex in powershell to make this happen. Enjoy.

PS SQLSERVER:\SQL\Server1\DEFAULT\Databases\ProdDB\StoredProcedures> dir | ?{$_.schema -eq “Bay” -or $_.schema -match “EBM”} | ?{$_.Name -match “Bay?_PR”} | %{$_.Script() | out-file C:\SPs.txt -append; “GO” | out-file C:\SPs.txt -append}

Maybe someday one of you will actually save the day with it.

Admin

Who’s afraid of cursors?

1 Comment

Ok, so one of my last posts talked about using a cursor for something and I came upon some criticism and/or some wincing about the fact that I used that horrible word.  However, cursors like anything have gotten a bad rap (xp_cmdshell is another).  People, there’s nothing wrong with using cursors as long as it’s what’s actually needed.  The problem comes with frontend coders writing cursors for everything and severely abusing this feature. 

But there’s nothing inherently wrong with them… esp for admin tasks.  There are so many legitimate uses for cursors I can’t even come close to naming them all, but here are a few…

Doing something to every DB on a server.

Doing something to every schema in a DB.

Doing something to every table in a schema.

Doing something to every user/login acct (like running changeUsersLogin to correct a ghosted user acct issue).

These are all perfectly legitimate uses of a cursor, whether it’s T-SQL or powershell.  So let’s get ove rthis hangup we’ve got against both the concept and the word.  There’s nothing wrong with it as long as you do it right.  Afterall, what is a cursor anyway?  It’s a way to store a list of values so you can perform an action on each one of them.  And the alternative is to hardcode each one of those lines yourself or use the GUI for everything and we all know that’s completely unacceptable. 

And in powershell everything you do is a cursor… even things that don’t need them.  Of course, they call it a foreach loop, but that’s just a more acceptable name for a cursor isn’t it?  It’s more specific… foreach one of these things, I want to do this…  hell, even almost every backup routine I’ve seen in the shops I’ve been in, and on the web cursor through the DBs on a server and back them up.

So deciding to not use cursors (or at least wince at them) because they get misused by some is like refusing to use a hammer on a nail because of someone you saw trying to open a banana with one.  It wasn’t the right tool for the job so it messed things up.  But it IS the right tool for a nail.

Uncategorized

The xp_CMDShell Curse

I interviewed a couple guys for a DBA position the other day and I asked one of my usual questions which is simply to discuss xp_cmdshell and its implications.  Without hesitation, I got the same answer twice.  Both guys gasped and talked about the evils of xp_cmdshell.  So I let them go on and then presented them with a couple scenarios that we discussed.  And again, all of this just makes me think about true evil and how xp_cmdshell just doesn’t fit.  See, this xp was developed for a reason;  It filled in the gap on some missing functionality, and it does it very well.  In fact, it does it so well, and the gap is so big, that xp_cmdshell gained in popularity and became an instant success.  And in fact, it was used too much and this is where it got its bad reputation.  Don’t hold that against xp_cmdshell though.  Just because everyone recognizes its usefulness doesn’t mean that it’s bad.  The problem started because everyone wanted to use it and the security was opened wide allowing damage to be done. 

But it’s not evil;  it’s just misused.  If you keep it locked down there’s no reason why you can’t have a fairly nice relationship with xp_cmdshell.  The same goes for cursors.  Cursors have also fallen prey to the cmdshell curse.  And again, it’s through misuse. So don’t kick either of these out of your shop because so many other devs are stupid.  Use them both wisely and they’ll do good things for you.