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.
I agree – although I always start from the premise that using a DC is “better” based on readability and performance. (Readability as in – you don’t have to know .Net, just SSIS expressions.)
When the expression gets complex, or during development, I also tend to break it into several DCs in series. This allows me to view intermediate results, and keeps performance up (at least in 2008) because each component will (likely) have its own thread.
It’s worth noting (although not important for most jobs) that the SC incurs a performance penalty because it crosses the COM/.Net barrier.
One of my frustrations with the SSIS development environment has been the single-line space for expressions in the Derived Column component. Would it have been so hard to add a button to open the Expression Editor, just like for other expressions? Oh well, maybe an update to BIDSHelper or another add-on will add that capability.