SSIS: Can I edit my recordset as I loop through it using the foreach statement?

Question

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.

Answer 1

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.