This is part 9 of the series of article on SSIS
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.
To follow my series of articles on SSIS packages refer to
the below URL
SSIS Articles Part 1 to 6
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 below 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
OnPostExecute: Triggered when task is executed
OnError: Triggered when error occurred with the
OnWarning: Triggered when task just throws a
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
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 below screen
Now go ahead and press F5 to run the application. It will
process the task and shows the execution after the tasks completed as shown in
the below 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 below 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.
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.