Is it possible change Object variables value in Script Task with ForEach then store it in Object variable?

Question

I use VS 2008 SSIS.

1. I have a Data Flow Task to get rows from table and store them in an Object variable.
2. I use Foreach Loop to get the Object variable's value one by one.
3. Inside my Foreach I use Script Task to change/decrypt my variable's value each time.

My problem is I cannot find how to store the decrypted values back to Object variable because I need them save to Flat File.

I used another Object variable and tried to save the decrypted values in it. But saves only last decrypted value not whole list.

Please share your thoughts or advice. Any help or suggestions would be greatly appreciated!

Answer 1

You can access the object variable without an SSIS Foreach Loop. The example below populates a DataTable from the object variable, updates the DataRow for the column named in the C# foreach loop, and writes this back out to the object variable. After this Script Task you can do whatever is necessary with the updated contents of the object variable, including writing to a Flat File Destination.

            DataTable dt = new DataTable();
            OleDbDataAdapter adapter = new OleDbDataAdapter();
            adapter.Fill(dt, Dts.Variables["User::YourObjectVariable"].Value);

            foreach (DataRow r in dt.Rows)
            {
                r["Column"] = " "; //Do work here

            }

            Dts.Variables["User::YourObjectVariable"].Value = dt;

Answer 2

Since your goal is to save the decrypted values to a flat file, you could create a final dataflow and use your object variable as a Source, do your decrypting in a Script Transformation (instead of a task), and send the results to your flat file destination.