SQL Server Integration Services (SSIS) - Breakpoints in SSIS Packages

Introduction

In this article we are going to look into the options to debug the SSIS package, mainly on the Break point options available with SSIS. As you all know breakpoints are nothing but a point where the developer can hold of and see the how the code executes at that particular point. In SSIS similar break point options are provided to check the status of the task at that particular point. SSIS breakpoints are provided with some events based on which we can track the execution plan and see how the process flows. We will see the step by step process on how to enable breakpoints and how to use the windows available in order to analyze the breakpoint execution.

Steps

Follow steps 1 to 3 on my first article to open the BIDS project and select the right project to work on integration services project. Once the project is created, we will see on how to use breakpoint options available with SSIS.

I have created a project here which will copy the data from SQL db to Excel sheet from the Northwind database. I have created a Dataflow task along with OLEDB Source and Excel destination tasks to make the flow perfect. Now in order to activate the Break points just right click on the DataFlow task and select "Edit Breakpoints" as shown in the following screen.

It will open a window which has the list of events available for the SSIS process execution. We need to select our exact needed break point to check the process at that particular point. There are about 10 events available and are as follows.

OnPreExecute: Triggered when task is about to Execute
OnPostExecute: Triggered when task is executed
OnError: Triggered when error occurred with the Task
OnWarning: Triggered when task just throws a warning
OnInformation: Triggered when task is about to provide some information's
OnTaskFailed: Triggered by task host when it fails to execute.
OnProgress: Triggered to update progress about task execution.
OnQueryCancel: Triggered in task processing when you can cancel execution.
OnVariableValueChanged: Triggered when the variable value is changed
OnCustomEvent: Triggered by tasks to raise custom task-defined events.

Here I have selected OnPostExecute event, so in my project once the task is execute this break point wil be triggered and we can check the execution process at that point.

Let's now see on how the breakpoint execution works, if you notice after selecting the break point a red dot will appear in the task as break point notification as shown in the following screen.

Now go ahead and press F5 to run the application. It will process the task and show the execution after the tasks have completed as shown in the following screen.

In the above image if you see it points to the RED dot with an arrow symbol which indicates that the execution is waiting at this breakpoint to get completed. If you see the following pane in the IDE there are some windows which tells the execution process of this task.

The LOCALS windows at the bottom tells you exactly on the execution status if its success or failure, and the duration of the execution process and the execution status. Similar wise on the right hand side we can see the Breakpoints window which shows the complete list of breakpoints available not specific to the task but to the whole application.

OUTPUT window shows the execution process on the steps done and shows what is available at the current section. If we go to the data flow tab it shows the execution in green color which confirms that the execution is completed and then the process breakpoint triggered.

Conclusion

So in this section we have seen on the break point essentials in SSIS Packaging and the execution plan status available in order to check the process flow.