Mindmajix

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
  1. In case of continuation  → go to debug menu → click continue.
  2. 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.

  1. 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

       Screenshot_49                                                            

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:

  1. Declare a variable called “counter”.
  2. Take a For loop and do the below settings

                     ↓

                          Rt click → properties:

                          Initial expression: @counter=6

                          Evaluation Exp.: @counter<9

                           Assign expression: @counter=@counter

 Screenshot_50

→  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)       

 Screenshot_51

2)

 Screenshot_52

 Note:  Work flow can be controlled by precedence constraints.

Enroll for Live Instructor Led Online MSBI Training


0 Responses on Debugging SSIS Packages in SQL Server"

Leave a Message

Your email address will not be published. Required fields are marked *

Copy Rights Reserved © Mindmajix.com All rights reserved. Disclaimer.
Course Adviser

Fill your details, course adviser will reach you.