Script Task Breakpoints in SQL Server 2008

by Allan Mitchell 23 Aug 2007 14:00

One of the things coming from our look at SQL Server 2008 Integration Services is that you are not going to see a lot of change visually. What you are going to see is things going on underneath the covers that makes SSIS perform better, and improved informational messages from the components. Just such a change is seen in the Script task.

In SQL Server 2005 we drop onto the designer two Script tasks and join them with workflow. We can then go into the Scripts of the tasks and set breakpoints in the code of each. The Control Flow should now look something like this.




Now if we jump to SQL Server 2008 and build the exact same control flow it looks a whole load different. Note the distinct lack of red breakpoint marks.




As we can see in SQL Server 2005 the designer alerts us to the fact that we have breakpoints set somewhere on or in each of the Script tasks. That said, when you execute the SQL Server 2005 package the breakpoint in Script Task (s1) will fire but the breakpoint in Script task (s2) will not. Why is not clear as there is no indication anywhere to tell us, so we are left scratching our heads. Here is the output from the Output Window describing what happened.

SSIS package "Package.dtsx" starting.
SSIS package "Package.dtsx" finished: Success.
The program '[876] Package.dtsx: DTS' has exited with code 0 (0x0).

When we execute the SQL Server 2008 package the breakpoint in Script Task (s2) still does not fire however the output window is a lot more informative.

SSIS package "Package1.dtsx" starting.
Warning: 0x6 at s2: Debugging of more than 1 script task is not currently supported.
SSIS package "Package1.dtsx" finished: Success.

SQL Server 2008 Books online is also more detailed in the explanation, having added this note:

When you debug a package that contains multiple Script tasks, the debugger hits breakpoints in only one Script task and will ignore breakpoints in the other Script tasks. If a Script task is part of a Foreach Loop or For Loop container, the debugger ignores breakpoints in the Script task after the first iteration of the loop.

Note though that having breakpoints set on the script tasks themselves and not the code within the Script task will cause both breakpoints to fire as expected i.e. on Events. It is only the breakpoints on code that have the restrictions detailed here. The breakpoint indicators will also appear on the task in SQL Server 2008 if the breakpoint is a task breakpoint.

Comments (1) -

12/9/2010 5:25:29 PM #


Thanks for that tip, I have been searching for ages why SSIS was not breaking on the 2 breakpoints. In regards to putting a breakpoint on the Script Tasks themselves not in the task itself:  Which break condition do you set? I.e. OnPreExecute event, OnPostExecute event?

Mike United Kingdom

Add comment

  Country flag

  • Comment
  • Preview

Widget Amazon Book not found.

Object reference not set to an instance of an object.X