Debugging Control Flow
Let’s clear existing breakpoints, if any. Select Debug>Windows>Breakpoints
This opens Set Breakpoints - <package name> window. This is how package level breakpoints are set. For this example, we will set breakpoints on OnPreExecute, OnPostExecute and OnVariableValueChanged. One can further configure breakpoints using Hit Count Type and Hit Count.
The Control Flow tab changes it appearance to notify that breakpoints are set at Package level.
Drag and drop For Loop Container on the Control Flow Task.
Right click on For Loop Container and select Edit. Have configured For Loop Container to use variable “i” and iterate 5 times. Click OK.
Right click on For Loop Container and select Edit Breakpoints… Select “Break at the beginning of every iteration of the loop”, to break at every iteration of the For loop. Click OK.
Right click on Execute SQL Task and select Edit Breakpoints… Select OnProgress event and click OK.
Breakpoints
Breakpoints can be enabled at the package level, or at the level of the individual component. When a breakpoint is hit, the breakpoint icon changes to help us identify the source of the breakpoint. We can add, delete, and change breakpoints when the package is running.
Integration Services provides ten break conditions that we can enable on all tasks and containers:
OnPreExecute - This event is raised by a task or a container immediately before it runs.
OnPostExecute - 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 we 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.
In addition some tasks and containers include special break conditions for setting breakpoints. For example, we can enable a break condition on the For Loop container that sets a breakpoint that suspends execution at the start of each iteration of the loop.
We can modify the behavior of a breakpoint by specifying the following options:
The hit count, or the maximum number of times that a break condition occurs before the execution is suspended.
The hit count type, or the rule that specifies when the break condition triggers the breakpoint.
Hit count type can be set to:
Always - Execution is always suspended when the breakpoint is hit.
Hit count equals - Execution is suspended when the number of times the breakpoint has occurred is equal to the hit count.
Hit count greater than or equal to - Execution is suspended when the number of times the breakpoint has occurred is equal to or greater than the hit count.
Hit count multiple - Execution is suspended when a multiple of the hit count occurs. For example, if we set this option to 5, execution is suspended every fifth time.
Progress Reporting
SSIS Designer includes two types of progress reporting: color-coding on the design surface of the Control Flow tab, and progress messages on the Progress tab.
The following colors are used to depict execution status:
Gray - Waiting to run
Yellow - Running
Green - Ran successfully
Red - Ran with errors
The Progress tab lists tasks and containers in execution order and includes the start and finish times, warnings, and error messages. After you stop package execution, the progress information remains available on the Execution Results tab.
To enable or disable the display of messages on the Progress tab, toggle the Debug Progress Reporting option on the SSIS menu.
Debug Windows
BIDS and SSDT include many windows that we can use to work with breakpoints, and to debug packages that contain breakpoints.
To open these windows in BIDS or SSDT, click the Debug menu, point to Windows, and then click Breakpoints, Output, or Immediate.
Breakpoints - Lists the breakpoints in a package and provides options to enable and delete breakpoints.
Output - Displays status messages for features in Business Intelligence Development Studio.
Immediate - Used to debug and evaluate expressions and print variable values.
The following example explains how to use breakpoints. In this example, we will see how to set breakpoints on package, task and container level.
Open SSISStudy / SSISStudy2012 project created earlier. Create new package and rename it as BreakPointExample.dtsx.
Let’s clear existing breakpoints, if any. Select Debug>Windows>Breakpoints
Select all the breakpoints in Breakpoints window and click Delete
Right click on the Control Flow area and select Edit Breakpoints…
This opens Set Breakpoints - <package name> window. This is how package level breakpoints are set. For this example, we will set breakpoints on OnPreExecute, OnPostExecute and OnVariableValueChanged. One can further configure breakpoints using Hit Count Type and Hit Count.
The Control Flow tab changes it appearance to notify that breakpoints are set at Package level.
Since we have set breakpoints on variable value change, we will create variables named “i” and “Cnt” of type int at package level.
Click on Choose Variable Columns and check mark to show all the columns. Click OK.
Create two variables and set Raise Change Event value to True. This is done to be able to fire VariableValueChange event when variable value changes.
Drag and drop For Loop Container on the Control Flow Task.
Right click on For Loop Container and select Edit. Have configured For Loop Container to use variable “i” and iterate 5 times. Click OK.
Right click on For Loop Container and select Edit Breakpoints… Select “Break at the beginning of every iteration of the loop”, to break at every iteration of the For loop. Click OK.
Finally let’s drag and drop Execute SQL Task inside For Loop Container. Right click and select Edit to open Execute SQL Task Editor. Configure as follows:
General
ResultSet: Single row
ConnectionType: OLE DB
Connection: SQL Server Instance
SQLSourceType: Direct Input
SQLStatement: SELECT COUNT(*) + ? AS Cnt FROM HumanResources.Employee
Parameter Mapping
Variable Name: Select variable “i” created earlier
Direction: Input
Data Type: LONG
Parameter Name: 0
Parameter Size: -1
Result Set
Result Name: Cnt
Variable Name: Select variable Cnt created earlier. Click OK.
Right click on Execute SQL Task and select Edit Breakpoints… Select OnProgress event and click OK.
Our final setup should look similar to this
Execute the package. We should see package execution halting for every event fired, i.e. for every breakpoints chosen. Click Continue (or F5) to continue package execution. Every breakpoint in Breakpoints window has TaskID information(i.e. ID of package or container or task). Expand or hover Name column in Breakpoints window to see the TaskID.
Additionally, to see the value of the variable changing at every package breakpoints, use Watch <1 or 2 or 3 or 4> window and type the variable names in every row and select next row i.e. i and Cnt for this example.
No comments:
Post a Comment