Wednesday, May 4, 2011

SSIS package variables cannot update and use inside a dataflow task

In this article I am going to introduce an alternative solution for a common problem in SSIS. That is updated values of a SSIS package variables cannot use inside of a dataflow task. I found this problem when I was going to Increase the ErrorCount, if any missmatches found during the lookup. A sample scenario is demonstrated below.

In the above scenario, I have used Row Count component to get the row count of the lookup no match output. I used variable called Count to store the row count. In  the script component I increment the ProductErrorCount variable if row count (value of the Count variable) greater than zero. At the end I found that no count had been updated, during the execution of dataflow task. Therefore above method cannot be used to get the row count of a specific path.

The reason for the above problem is SSIS package variables does not update during the execution of data flow task. Those variables get updated once the Data flow task finishes. Therefore we cannot use the updated value of the variable during the execution of a dataflow task.

Due to the above error we cannot change the value of a variable within the Process method of a Script component. If you try to change the value of a variable, you'll get the following error.

Eventhough you could change the variable values within pre-execution or post-execution methods, it only take effect to the value of the variable after the execution of the data flow task.

Then I use the following alternative method to get the row count without using package variables.

I create a grobal variable called Count inside the script component. Then I increment its value within the process method. If script component read hundred records, then Count global variable incremented up to hundred at the end of the process method. Then In the post execution method I incerement the package variable ProductErrorCount, so I can use its value at the Control flow.  

I hope the above method might helpful in your scenarios. But this is only a alternative solution, Its not a correct way to solve the problem. If anybody found any good method please let me know. Your comments are highly appritiated.

1 comment: