Debugging SSIS Packages in SQL Server

Debugging SSIS Packages

There are three ways of debugging. They are:
1) By executing the package partially
It multiple tasks are present in a package then we can execute a specific task.
Rt click that taskàclick execute task.
2) By break points.
To stop the execution of package at a particular “event” and to continue or stop the event, break points are used.
We perform break points only in control flow.
Consider the (fig: a), taking break point after df-update successful.

Navigation:
DF-update → rt click → edit break point → mark break while the container receives the on post execute event option → click ok.
Execute the task so there is a break after DF-Update.
Monitor the statistics or observe the expected result, based on the result
In case of continuation → go to debug menu → click continue.
In case of stopping → go to debug menu → click stop debugging.

Hit count:
We will take it along with the break point to have a break according to the condition. This is applicable and usable. Generally when we are having containers like “for loop” etc. (except the type always).

Types
1. Always: Execution is always suspended when the break point is hit.
2. Hit count equal to: Execution is suspended when the no.of times the break point has occur is equal to hit count.
3. Hit-count greater than or equal to: Execution is suspended when the no.of times the break point has occur is equal to or greater than the hit count.
4. Hit count multiple: Execution is suspended when multiple of the hit count occurs.
Eg: If we set this option to five, it takes a break for every fifth time.

Data viewer:
These are used only in the data flow task between source, destination and transformation we take this data view option only in the links between the components.
Go to any link in the data flow task → rt click → data viewers → click add → select grid → ok

Precedence Constraints

These are useful to control the flow between various tasks in control flow.
Constraints options: There are two evolution operations.
a) Constraint:
Success (Green link): if previous component is succeeded it executes the other.
Failure (Red link): if the previous component is failed it executes the other.
Completion (blue link): if the previous component is either success or failure it runs the other.
b) Expression → It support an expression when it satisfies or reaches it executes the other task.
Eg: @counter=6
Counter is a variable (may be for loop or any).
c) Expression and constraint → If both are satisfied then only the other task executed.
d) Expression or constraint → If either of these are succeeded it executes the other task.

Implementation of expression:

Declare a variable called “counter”.
Take a For loop and do the below settings
↓
Rt click → properties:
Initial expression: @counter=6
Evaluation Exp.: @counter<9
Assign expression: @counter=@counter

→ Rt click the link → edit
Evaluation operation: expression and constraint
Value: success
Expression: @counter==6

Multiple Constraints

We take multiple constraints to interoperate and to control the execution of the constrained task.
Two ways:

Logical AND, all constraints must evaluate to true
Logical OR, one constraint must evaluate to true.

1)

2)

Note: Work flow can be controlled by precedence constraints.

Enroll for Live Instructor Led MSBI TRAINING

Blog

Debugging SSIS Packages in SQL Server