Iterate A Variable

Hi Guys,

I need to design this SSIS migration package to migrate data.

In a Execute SQL Task, I need to get a full result set and assign it to an variable, such as v_collection;

The SQL statement can be as simple as : select primary_key from a_table;

After that I have a ForEach Loop container, that consumes the variable, and assign each iteration to another variable, such as v_iter, the type of v_iter is DT_I4, because the primary key is a long integer

The problem is: in Oracle, the primary key is NUMERIC(10,0) and in SQL it is int.

I can not assign a NUMERIC(10,0) to an variable of DT_I4, but if I change the variable definition to DT_NUMERIC, then it would not work for SQL.

Anyone knows how to fix this?

I was thinking to add a Script Task between the Execute SQL Task and the Foreach Loop, and somehow access the collection variable, v_collection, and manually convert the value to a DT_IT, then repopulate another collection variable, v_collection_I4 with Integers, and force the Foreach Loop to use v_collection_I4 collection variable.

Will this work? If yes, how? :)



Thanks a lot!

Wenbiao