Debugging Packages

Data viewers are one tool you can use when you need to debug packages. Placed between two tasks, a data viewer shows what the data looks like at that point in the process. If the data looks good so far, you’ll know that the problem is occurring further along in your package, or if the data clearly is not correct, you’ll know that it happened prior to the point where you are viewing it.

Setting up a data viewer is easy, as you’ll see in Exercise 6.3.

Exercise 6.3: Debugging Using a Data Viewer

In this exercise, you’ll use a data viewer:

1. Click the Data Flow tab in SSIS, right-click the line (the wire) between the Derived Column transform and the Excel destination, and then select Data Viewers.

2. Data viewers will be selected by default. Click Add.

3. There are four types of viewers, as shown here; the grid is the default viewer and is a good place to start. The Grid tab allows you to customize the columns you view. By default, all columns are included. Click OK to proceed.

   

4. Now back at the Data Flow tab, you can see the small image with glasses that indicates a data viewer is loaded.

5. Execute the package while on the Data Flow tab. All the tasks in the data flow will turn yellow, indicating they are in progress, and the data viewer will pop up, as shown here. You can browse the data (but not change it), copy the data to the clipboard, detach the viewer if you no longer need it, or click the green arrow to allow execution to continue. It’s important to understand that until you click the arrow or detach, the execution of the package is paused.

   

6. Breakpoints are another useful tool for debugging. You can use breakpoints in two places; the first is in the control flow, and the second is within a Script task. You’ll start by looking at how to manage breakpoints in the control flow. Right-click the Data Flow task, and select Edit Breakpoints.

7. You can set a number of different breakpoints, and for each of them you can set the Hit Count Type to Always Fire or to Fire Based on a Hit Count Threshold. For this example, set it to always fire when the OnPreExecute event occurs. Run the package, and you should see the package pause with a red circle with a yellow arrow in it on the Data Flow task. If the Locals window is not showing, you can display it from the top menu by choosing Debug Windows Locals. You should then see something like the screen shown here.

   

8. Breakpoints in Script tasks work a little differently. Double-click the Script task, and click Design Script to open the code editor. Set or remove breakpoints by putting the cursor on the line and pressing F9. When the package runs, execution will stop at the breakpoint.

9. The final tool you may use for debugging is logging. To get to the logging dialog box, choose SSIS Logging. Select a provider type, and click Add.

10. Set up a new configuration to tell SSIS how to connect to the log (provide a path and filename for text file logging, for instance, or credentials to a SQL Server if you use SQL logging).

By using data viewers, breakpoints, and logging, you should be able to identify most problems. In the toughest cases, you might need to remove steps from the package until you can get it to successfully execute and then add them back one at a time.