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.
There are three ways of debugging. They are:
It multiple tasks are present in a package then we can execute a specific task.
Rt click that taskàclick execute task.
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
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.
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).
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.
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
These are useful to control the flow between various tasks in control flow.
Constraints options: There are two evolution operations.
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.
It support an expression when it satisfies or reaches it executes the other task.
Counter is a variable (may be for loop or any).
If both are satisfied then only the other task executed.
If either of these are succeeded it executes the other task.
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
We take multiple constraints to interoperate and to control the execution of the constrained task.
Note: Work flow can be controlled by precedence constraints.
|SSAS||SQL Server DBA|
|Team Foundation Server||BizTalk Server Administrator|