SSIS Script tasks provide codes to implement customized functions that a built-in SSIS task cannot achieve directly. It is often necessary to debug through a Script task to ensure it works properly. You can set breakpoints for events such as OnPreExecute or OnPostExecute from ten break conditions as we discussed in Set Breakpoints for SSIS Debugging. Moreover, you can define stopping points in a script task through Microsoft Visual Studio Tools for Applications (VSTA).
Set a breakpoint in VSTA for a Script Task?
After you click “Edit Script” button in Script Task, a VSTA window will open.
Click the line you would like to set a breakpoint. Then right-click it and select Breakpoint –> Insert Breakpoint.
Below shows setting breakpoints in SQL Server 2008 R2.
Now you have the breakpoint set right before Messagebox.Show(). This means that the execution should break immediately before the message box is popped up.
SQL Server 2012
The way to set breakpoints in Script Tasks for SQL Server 2012 is similar as shown.
Also, after clicking Insert Breakpoint, a popup window will let you to do more fine tunings for your breakpoint through locations of Line and Character.
Note that SQL Server 2012 has one additional option: Insert Tracepoint besides “Insert Breakpoint”.
How breakpoints work?
After you set up the breakpoint, you can close Script Task Editor window. Right Click the Script Task and then select Execute Task. You would expect a yellow arrow displayed at the breakpoint.
After you press F10 button, a message box pops up with the message “Hello World”. After you close the popup window, the VSTA window should appear like this.
Tweaks for SQL Server 2008
If you use SQL Server 2012, you are lucky to get the breakpoint hit as described above.
If you use SQL Server 2008, you need some tweaks to make breakpoints working as expected:
Step 1: Set SSIS runtime mode as 32 bit at PROJECT Level.
Step 2: Re-open Script Task Editor window and save it again. In this way, your script task code is set to be compiled as 32 bit.
Shortcut Keys for Debugging
You can use debugger shortcut keys to speed up debugging. SSIS Script tasks share the same set of shortcut keys for Visual Studio. Here is a list of some common shortcut keys.
| Keys | Functions |
| F5 | Run the application. |
| F10 | Step Over (Execute the next line of codes but not follow execution through any function calls). |
| F11 | Step Into. |
| SHIFT+F11 | Step Out. |
| CTRL+BREAK | Stop execution (Break). |
| F9 | Toggle breakpoints. |
Now I hope that you have a solid understanding of how to debug script tasks in SSIS. Take time to play with it and you will find it save the day for you in troubleshooting. If you have any questions, please feel free to leave a comment.
No comments:
Post a Comment