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

Question

I use VS 2008 SSIS.

I have a Data Flow Task to get rows from table and store them in an Object variable.
I use Foreach Loop to get the Object variable's value one by one.
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!

userfl89 · Accepted Answer · 2018-09-11 15:22:17Z

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;

answered Sep 11, 2018 at 15:22

userfl89

4,8201 gold badge13 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Ange Over a year ago

Thank you for your suggestion. I used DataTable in Script Component. Then it worked! Thanks again!

Tab Alleman · Accepted Answer · 2018-09-11 15:02:35Z

0

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.

answered Sep 11, 2018 at 15:02

Tab Alleman

31.8k7 gold badges41 silver badges54 bronze badges

1 Comment

Ange Over a year ago

Thank you for your clue about Script Transformation! I used Script Component with DataTable from above comment. Now it works :)

Collectives™ on Stack Overflow

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

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related