How To Debug SSIS Packages in SQL Server

Learn How to debug SSIS package. Debugging is one of the most exciting areas of SSIS development, so as an SSIS developer you need to know how to get bugs in packages.

How To Debug SSIS Package

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.

Learn how to use MSBI, from beginner basics to advanced techniques, with online video tutorials taught by industry experts. Enroll for Free MSBI Training Demo!

If you would like to enrich your career, then visit Mindmajix - a global online training platform: SQL Server Online Course this course will help you to achieve excellence in this domain

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: Execu tion 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:

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

1)       
 
2)
 
 Note:  Work flow can be controlled by precedence constraints.

List of Related Microsoft Certification Courses:

SSIS	Power BI
SSRS	SharePoint
SSAS	SQL Server DBA
SCCM	BizTalk Server
Team Foundation Server	BizTalk Server Administrator