I am using SSIS to make a series of complicated data exports, and I've hit a roadblock. I've populated a package variable with a recordset. I'd like to use a foreach loop to iterate through each row in the recordset. I'd like to update one of the columns in each row based on some calculations which I've done inside a script task.
Is this possible? I know in C# the foreach collection is immutable, but I don't know if SSIS works the same way.
Unfortunately, I haven't found any good examples of using the for loop construct instead, which might be a potential solution.
When you put data into a recordset it's stored in an object variable. You can use the Foreach Loop Container - and loop the object variable. You then create some variables to hold each column for the row - and you then have a row-by-row ability to do whatever you please, be it a data flow task, sql statement, script task (c#) or anything else.
See http://www.sqlis.com/post/Shredding-a-Recordset.aspx for an illustrated example of how to do this and send an email for every row.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With