SSIS: Script component debugging comes to SQL Server Integration Services 2012

jamiet — Fri, 14 Oct 2011 08:48:00 GMT

One of the biggest complaints I see developers raising about SSIS since it was released in 2005 is that the only way to debug Script Components is the distinctly simplistic and old fashioned method of sticking up message boxes - there is no way to step through the code as it executes (something that Microsoft developers have generally been able to do for eons).

Happily that is no longer the case, or won't be soon anyway. In his session yesterday at the PASS Summit SSIS team member Matt Masson revealed that proper Script Component debugging (breakpoints, step into, step over, etc...) will be coming to SSIS 2012. Matt asked me to point out that nothing is certain, things may change before RTM blah blah blah but ostensibly SSIS developers can now sleep easy in the knowledge that message box debugging has gone away forever. Great news!

@jamiet

Don’t turn the dataflow into a cursor [SSIS]

jamiet — Tue, 31 Aug 2010 21:22:00 GMT

I saw a thread on the SSIS forum today that went something like this:

I have the following dataset:

AccountNo Date DailyMovement

00000001 28/08/2010 10

00000001 29/08/2010 5

00000001 30/08/2010 7

00000002 28/08/2010 8

00000002 29/08/2010 6

for which I want to compute a running total per [AccountNo] & [Date] like so:

AccountNo Date DailyMovement RunningTotal

00000001 28/08/2010 10 10

00000001 29/08/2010 5 15

00000001 30/08/2010 7 22

00000002 28/08/2010 8 8

00000002 29/08/2010 6 14

How do I do that using a script component?

That last comment:

How do I do that using a script component?

is a fairly common question. People assume that if a calculated value is dependant on prior rows then a script component needs to be involved because that running total needs to be tracked somewhere, but that isn’t necessarily the case. Ask yourself, how would you do this if the data were residing in a database table, would you do this?:

SELECT '00000001' AS [AccountNo], CONVERT(date,'20080828') AS [Date],10 AS [DailyMovement]            
INTO   #balances            
UNION  ALL            
SELECT '00000001' AS [AccountNo], CONVERT(date,'20080829') AS [Date],5 AS [DailyMovement]            
UNION  ALL            
SELECT '00000001' AS [AccountNo], CONVERT(date,'20080830') AS [Date],7 AS [DailyMovement]            
UNION  ALL            
SELECT '00000002' AS [AccountNo], CONVERT(date,'20080828') AS [Date],8 AS [DailyMovement]            
UNION  ALL            
SELECT '00000002' AS [AccountNo], CONVERT(date,'20080829') AS [Date],6 AS [DailyMovement]            
            
SELECT [AccountNo]            
,      [Date]            
,      [DailyMovement]            
,      (   SELECT  SUM(DailyMovement)            
           FROM    #balances b2            
           WHERE   b1.[AccountNo] = b2.[AccountNo]            
           AND     b1.[Date] >= b2.[Date])            
FROM   #balances b1;

OK that works, and here’s a screenshot to prove it:

But really, would any of you actually do this? Hopefully not, the use of the correlated subquery has simply turned what should be a nice set operation into a cursor-in-disguise (something I have talked about before) because that subquery will be getting executed for every row in the table. Instead you could run the following on that same dataset:

SELECT b1.[AccountNo] , b1.[Date] , b1.[DailyMovement] , SUM(b2.[DailyMovement]) AS [RunningTotal] FROM #balances b1 INNER JOIN #balances b2 ON b1.[AccountNo] = b2.[AccountNo] AND b1.[Date] >= b2.[Date] GROUP BY b1.[AccountNo],b1.[Date],b1.[DailyMovement];

and you get the same result but with a much more amenable execution plan (execute with SET STATISTICS IO ON if you don’t believe me)!

The same principle applies in a SSIS dataflow. Often there is no need to resort to a script component, the same result can be achieved using some smart dataflow design. You can use, for example, the same approach as in the second SQL query (above) using a combination of SSIS’ Sort, Aggregate, Merge Join and Conditional Split components. That is not to say that one will be quicker than the other but at least you’ll have something that is more intuitive and arguably more maintainable. Of course if performance gain is your primary goal then the correct advice is, as always, “test and measure, test and measure, test and measure”!!!

Ask yourself “If I could, how would I solve this using T-SQL?” and see if that influences your dataflow design at all. Invariably script components should be the last choice, not the first!

@jamiet

AccountNo	Date	DailyMovement
00000001	28/08/2010	10
00000001	29/08/2010	5
00000001	30/08/2010	7
00000002	28/08/2010	8
00000002	29/08/2010	6

SSIS Junkie : script component, ssis

SSIS: Script component debugging comes to SQL Server Integration Services 2012

Don’t turn the dataflow into a cursor [SSIS]