I had an opportunity to attend my first Tampa SQL BI Meeting last night and I wanted to share some of my thoughts from the SSIS demo that was given.
The first thing that really caught my attention is complexity of expressions that are used in a conditional split. I started picturing the nightmare scenario of debugging it and figured there has to be a better way. Enter the Script Component. The script component allows you to define multiple outputs and by monkeying (yeah thats a technical term) with the ExclusionGroup property you can filter the input rows. I am probably biased towards this technique (since my background is primarily in software development) and will need to find the time to do some analysis to see if there is a performance difference between the two methods.
The second item that got my thinking during the demo is more of a difference in approach. The case that was being presented had a requirement that before the package did any work a rowcount on the source data had to occur. If the row count returned 0 no further processing occured otherwise the destination was truncated and reloaded. To accomplish this task the presenter used an 'Execute SQL Task' to run the count query and then used to a 'Script Task' to determine if the control flow should proceed.
My thought on accomplishing this 'requirement' is a little more condensed. I would use the 'Execute SQL Task' but instead of a 'Script Task', I would use a 'Precedence Constraint' to modify the flow if necessary. By writing a simple expression to determine if the variable assigned to in the 'Script Task' is greater than zero you could either continue with required work or direct to an error flow.
Thats pretty much all for today...
Tight Lines!!
--Chris