31 Days of SSIS
As I mentioned in the introductory post, I’m writing 31 Days of SSIS. Today’s post is going to deal with one of the pieces within SSIS that is often overlooked with troubleshooting SSIS packages. This is the ability to set Breakpoints in SSIS packages.
Much like running projects in other languages through Visual Studio, you can also stop SSIS packages mid-execution when specific events occur in the Control Flow. This ability allows you to stop the execution of a package to investigate and troubleshoot the state of the SSIS package.
There are ten events in the Control Flow (definitions from Books Online):
- OnPreExecute: Called when a task is about to execute. This event is raised by a task or a container immediately before it runs.
- OnPostExecute: Called immediately after the execution logic of the task finishes. This event is raised by a task or container immediately after it runs.
- OnError: Called by a task or container when an error occurs.
- OnWarning: Called when the task is in a state that does not justify an error, but does warrant a warning.
- OnInformation: Called when the task is required to provide information.
- OnTaskFailed: Called by the task host when it fails.
- OnProgress: Called to update progress about task execution.
- OnQueryCancel: Called at any time in task processing when you can cancel execution.
- OnVariableValueChanged: Called by the Integration Services runtime when the value of a variable changes. The RaiseChangeEvent of the variable must be set to true to raise this event.
- OnCustomEvent: Called by tasks to raise custom task-defined events.
I’ve mentioned this twice now, and thought it should be called out yet again. Breakpoints function only at the Control Flow. If you need to break the action in a Data Flow, this isn’t the solution for that. This is called foreshadowing.
Breakpoint Demonstration
To implement a breakpoint in the Control Flow, start by right-clicking on a task in the Control Flow. In the menu, the option to Edit Breakpoints will be about half way down this list.
Select this menu item and you can choose between any of the breakpoints described above. For this example, we’ll select the OnPostExecute breakpoint. As mentioned above, this event will fire after the task’s execution has completed.
Let’s run the package now to see what happens. First you’ll notice that the the task has a red rot added to it, this indicates that there is a breakpoint on the task. Next when the breakpoint is reached, a little yellow arrow is added to the breakpoint icon.
Once a breakpoint has been used there are a number of things that can be used to get some information on the breakpoint. To start with there is the Call Stack window. This window provides information on where the breakpoint is located.
Next there is the Breakpoints window. Guess what it has? Yes, all of the breakpoints in your project will be listed here. There are couple points of interest and points of usefulness with this window.
First, the area in blue is all of the breakpoints in your package. From this point you can enable and disable them. A very useful feature in case you have many breakpoints in your package.
Second, the area in red is a control panel for your breakpoints. There are options to delete single breakpoints, delete all breakpoints in a project, disable all breakpoints in the project, and change the columns returned. There is additional functionality that can be used as well in advanced scenarios.
There is also an Output window that displays messaged on the progress of the package and breakpoint. This information will help determine what has been run in the package when the breaks occur.
Finally, the most important piece that breakpoints bring to you is the Locals window. This window will contain information on the current status of the package execution and the amount of time in which the package has been executing.
Most importantly, it contains all of the package variables. Often when I need to troubleshoot some odd package behavior it is related to variables not being populated as I thought they should. This is the golden ticket to finding out what the package is doing during execution.
Breakpoint Wrap-Up
Breakpoints are an essential tool in troubleshooting SSIS packages. Take time to understand how they work and practice using them a few times so that when the time comes you are ready to pull them out. They’ve gotten me through a few package executions and will probably save the day for you some time in the future.
This post scratches the surface on using breakpoints. There is a lot more that can be covered and other scenarios for their use. But those will have to wait for another day. Maybe one of the other 29 more posts in this series.
References
Breakpoints in SQL Server 2005 Integration Services SSIS
Related posts:
- 31 Days of SSIS – The Introduction
- 31 Days of SSIS – Raw Files Are Awesome (1/31)
- SSWUG Free SSIS Expo
