Tuesday, July 19, 2011

Issue in reading object variable in Script Task in for loop in SSIS

Issue in reading object variable in Script Task in for loop in SSIS.


While working with SSIS object variable I have faced some issue when I used it in loop and not able to get any proper solution on web. I find out some work around.

Problem:

 I have created a package in which I was getting the result set from database into an object variable using Execute SQL task. While iterating it in a loop the object was showing null values after first iteration. Script task was throwing an error after second iteration.

Analysis:

It seems the issue with the object reading of the .net. While filling data in to data table through object for first time it traverse entire result set. For second times it doesn’t retrieves the value from object in script task. This behavior is the know problem in SSIS for object variable.


Solution

For this we have added the data fill task for object in loop itself so it fills the data in the object every time in beginning of the loop.

No comments:

Post a Comment