When developing an SSIS package, sometimes it can be frustrating trying to troubleshoot various issues and faults with the package. And if you’re not a seasoned SSIS developer, you may not be sure about the best way to solve your problem. Thankfully, Microsoft has seen fit to bless us with some useful tools for troubleshooting.
Control Flow Troubleshooting
When working in the Control Flow, there are several useful ways to troubleshoot during the development of your SSIS Package:
1) Use breakpoints! Breakpoints can be applied to an individual task or at the package level. A break point will pause the execution of the package at a certain point. You can specify a package to pause at pre-execution, post-execution, on warning, or on error event of a single task, container or package, just to name a few. To enable a breakpoint, right click the task you want to enable your breakpoint on and select Edit Breakpoints. You’ll then be able to specify on which event you would like to pause the package. After adding a breakpoint to a task, you will see a red dot on the task. During execution of the task, you will see the red dot change. To continue execution of a package after it has paused at a breakpoint, simply click the green “Play” button.
2) Check the progress tab! The Progress tab displays valuable information about the execution of your SSIS package. In the Progress tab, SSIS will list all tasks in order of execution and display start and finish times at the task and package level. It even contains message about any errors or warnings that may have occured during execution. If your package failed, this is where the error will be displayed!
3) Use the Debug Windows! The various Debug windows provide you with valuable information during the execution of a package that has breakpoints. Error messages and warning are displayed in the Debug Output window, as well as value of variables during execution. To view the various Debug windows, add a breakpoint at the Pre-Execute event of the package and execute the package. Then in the main menu at the top, click Debug, select Windows, and you will be able to select the various debugging windows. To view the values of variables during run time, open the Watch window and type in the name of the variable you’d like to watch. The watch window will then display the data type and value of any variable you’d like to see.
Data Flow Troubleshooting
Troubleshooting the execution of a Data Flow Task is a little different than troubleshooting the Control Flow because you cannot add a breakpoint to a Data Flow Transform. But there are still tools available to us as developers.
1) Use Data Viewers! Data viewers allow us to watch the data as it passes between transforms. A data viewer can display data in a grid, histogram, scatter plot, or column chart format. To add a data viewer to a Data Flow, simply right click a precedence constraint and select Data Viewers. You can then Add and Delete a data viewer.
2) Use a Conditional Split as a destination! This is kind of a little trick that SSIS developers use that allow you to execute a data flow without writing to a destination. Simply use a Conditional Splitl as the last transform in place of a destination in the data flow and place a data viewer on the precedence constraint directly before the Conditional Split. You can then copy and paste the data from the data viewer into Excel or Note Pad for further review. Or if your place of employment has blessed you by purchasing Task Factory, simply use the Terminator Destination, which requires no configuration and acts as a dead end for your Data Flow during development.
3) Use Row Count transforms! Placing a Row Count directly after the source and directly before the destination in a data flow will allow you to closely monitor the number of rows passing through your data flow.
4) And lastly, use the Progress Tab and Debugging Window just like when you are troubleshoot your Control Flow.
Even while using each and every last one of these tips I’ve covered, every once in a while you’re going to come across on error that offers no real explanation or solution. Which brings me to my final and most important tip to remember when trouble shooting your SSIS package: GOOGLE! 