Debugging SSIS Script Tasks

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.

Reference:

1. Debug a Script by Setting Breakpoints in a Script Task and Script Component
2. Debugger Shortcut Keys for Visual Studio

How to: Run SSIS in 32 bit Mode

SSIS is an excellent tools for extract, transform, and loading data. Nowadays, more and more machines that SQL Server runs on are 64 bit. However, there are some conditions that require SSIS to be run in 32 bit mode, such as

1. when SSIS tries to interact with Microsoft Excel or Access files that only support 32 bit.

2. ActiveX script task or Execute DTS 2000 package task.

3. Debug script tasks.

Sometimes if it is not set in a right runtime mode, you will get error messages like:

Error: 0xC00F9304 at Package, Connection manager "Excel Connection Manager": SSIS Error Code DTS_E_OLEDB_EXCEL_NOT_SUPPORTED: The Excel Connection Manager is not supported in the 64-bit version of SSIS, as no OLE DB provider is available.

Or even without error messages when you try to get your script task pause at a breakpoint inside the script. It seems never hit the breakpoint and does not pause as expected.

How to set 32bit runtime mode at PROJECT Level?

After the project is opened in BIDS, right-click the project on the Solution Explorer panel and then click Properties.

Then on the Property Pages, first select Debugging under “Configuration Properties” on the left. Then change the property for Run64BitRuntime to be FALSE. (The default value for Run64BitRuntime is True.)

 

Now every package under this Demo Debug Project is set to run in 32 bit.

How to set 32bit runtime mode at PACKAGE Level?

To set 32bit runtime mode at PACKAGE Level means that you can run just one package in 32 bit. This can be accomplished through SQL Agent job settings.

It is required to have X86 DTExec.exe installed in order to have SQL Agent job to execute in 32 bit mode. 32 bit version of DTExec.exe is often installed at

C:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn\DTExec.exe (for SQL Server 2008)

C:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn\DTExec.exe (for SQL Server 2012)

As shown below, when you schedule jobs, you can set the Execution Options in job steps to “Use 32 bit runtime”.

If you execute the package using DTEXEC via command-line scheduling process, you need to specify the 32-bit version of DTEXEC by explicitly running the DTEXEC.EXE from the right folder as shown:

"C:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn\DTExec.exe" /f C:\PackageFolder\Package.dtsx /Conf C:\ConfigFile.dtsConfig