Home / MSBI

How To Debug SSIS Packages in SQL Server

Rating: 5.0Blog-star
Views: 12784
by Raunaak
Last modified: March 19th 2021

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
      Data viewer         

 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
 Implementation of expression
→  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)       
 Multiple Constraints
2)
 Work flow
 Note:  Work flow can be controlled by precedence constraints.

Explore MSBI Sample Resumes! Download & Edit, Get Noticed by Top Employers!Download Now!

List of Related Microsoft Certification Courses:

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

 

About Author

author
NameRaunaak
Author Bio

As a content writer and storyteller, Raunaak Mitra regards himself to be a prodigy in writing. He firmly believes that language is borderless, and anyone can write as long as they have a strong narrative capability and the ability to emote feelings into words. Authors like F. Scott Fitzgerald and R. K. Narayan have influenced him to keep the writing as simple as possible for anyone to understand. Other than being well-versed about technological trends like AI, Machine Learning, AR, VR, Gaming, Microprocessors, Cloud Computing, Industry 4.0, literally any technological advancement (old or new), he also shares a knack to curate fiction on sci-fi, satire, and thriller genres.