SSIS - How to Change Value of Variable During Debugging in SSIS Package

Scenario:

We are doing unit test of our SSIS Package or debugging. We are storing row count in RowCnt variable in our First Data Flow task. Depending upon the value of RowCnt variable ,we have set up Precedence Constraints. If the RowCnt is greater than or equal to 10 we want to Execute one Data Flow Task and if RowCnt is less than 10 then we want to Execute Second Data Flow task. It is very common scenario that we even don't have exact number or records in source to test both parts of SSIS Package. This is just one example, there could be so many other situations where you want to have the control  to change the value of variable/s so you can test different parts of SSIS Package while doing Unit Testing or Debugging.

Solution:

We will be changing the value of variable in Variable Watch Windows. Let's walk through above example step by step.

Step 1:
I have created RowCnt variable as shown below
Fig 1: Create RowCnt Variable in SSIS Package

Step 2: 
Bring Data Flow Task to Control Flow Pane and then create connection to Database or create any source connection as per your source. I have created Database connection and extracting data from SQL Table.
Inside the Data Flow Task, I have connected OLE DB Source to Row Count Transformation to save count in RowCnt variable.
Fig 2: Use Row Count Transformation in Data Flow Task to save Record Count

Step 3: 
Bring two Data Flow Tasks and connect to first Data Flow Task as shown in below Figure. Double click on green connection between them and write expressions.If you need help with using Precedence Constraints, please check This Post.

Fig 3: Configure Precedence Constraint in SSIS Package between Tasks

Step 4:
Now the fun part start, we want to enable Break Point on Data Flow Task so we can see the value of variable and change to test different scenarios. Right Click on Data Flow Task and then Select BreakPoint. It will open a window. Check the box as shown below
Fig 4: Enable BreakPoint in SSIS Package

I have chosen Break when the container receives the OnPostExecute event. By choosing that I want to make sure all the records are read in RowCnt variable inside Data Flow Task.

Step 5:
Execute the SSIS Package and see the value of RowCnt variable in Watch window. If you are not sure how to get to Watch window. You can check This post that shows you step by step how to configure it.
Fig 5: Variable Watch window in SSIS Package

As we can see that the value of RowCnt=9 and according to our expressions on Precedence Constraint, Data Flow Task 2 should execute. Let's hit play button and see if that works.
Fig 6: Use Precedence Constraint to Control Execute Flow in SSIS Package

Step 6:
To test the second part where our Data Flow Task 1 should execute. Either we have to have more or equal to 10 records in table or we should be able to change the value of variable while executing our SSIS Package. We are going for second option as it is easy and quick and we don't have to worry about making any changes on Source side.
Let's run the package one more time and go to Variable Watch window.
Fig 7: SSIS Variable Watch Window to Change Variable value while Execution

Double click on value that is 9 and change to 11 so we can test Data Flow Task 1. Once you will change is the 9 will change the color to red but in front of User::RowCnt the value will be changed from {9} to {11}
Fig 8: Change the value of variable in SSIS Package during debugging

Now click on Play button (Start Debugging) or Fit F5. As the value of value of RowCnt=11 now the Data Flow Task 1 should execute.
Fig 9: Modify value of variable during troubleshooting SSIS Package

As we can see that the RowCnt value did change and we were able to test both parts of SSIS Package without making any changes in source data. Hope this will help!








3 comments:

  1. North Shield Windows and Doors offers a wide selection of high-quality windows and doors. Among other features, our products offer the latest in security, durability, and design. We are homeowners too, that’s why we work within your style preferences to find exactly what you need and provide you with durable products that will keep your home safe.Windows replacement

    ReplyDelete
  2. Thanks for sharing very informative post with us. I also want to share some information about us: We, Delco Windows and Doors is one of the top rated window and doors company, serving since 1989. Contact with us for getting new windows in Toronto

    ReplyDelete
  3. With the help of wr1ter masterclass, you will be able to write an expository essay that is both informative and engaging, and impress your readers with your writing skills. So, if you want to learn how to write an expository essay like a pro, study expository essay body paragraph example link at Wr1ter.com today!

    ReplyDelete