Using a variable from Foreach Loop Container in a SQL Task [SSIS]

Question

Ok, I have a simple process...

1. Read a table and get the rows that have a "StatusID" of 1. Simple.

   Select ProductID from PreorderStatus where StatusID = 1

2. Foreach row returned from that query, perform an action. For simplicity sake, let's just modify the original table to set the "StatusID" to 2.

   Update PreorderStatus set StatusID = 2 where ProductID = @ProductID

In order to do this in SSIS, I have created a simple "Execute SQL Task" with the first statement. In the editor I have set the Result Set to return a Full result set and the Result Name of 0 is set to fill an object variable named ReadySet.

The output is then routed to a For Each Loop container. The Enumerator is set to Foreach ADO Enumerator and the object source variable set to the ReadySet variable from above. I have also mapped the variable v_ProductID to index 0.

Setting a breakpoint at the begining of the Foreach loop shows the variable being set correctly. GREAT!! Now on to step two....

Now I have placed a new SQL task in the foreach container. Now I have a head scratcher. How do I actually use the variable in the SQL statement. Simply using "v___ProductID" or "User::v_ProductID" doesn't seem to work. Mapping a parameter seemed like a good idea (got a @ProductID and everything!) but that didn't seem to work either.

I get the feeling that I am missing something pretty simple but can't tell what. Thanks for any help!!

Answer 1

When I want to use an execute sql task and vary something based on a variable, I use a stored proc and make the variable the input parameter for the proc.

Then you set the parmeter in the execute SQL task and set the SQL statement to something like:

exec myproc ?

Answer 2

I think there is a better approach. Here are the approximate steps:

1. Drag a DataFlow task onto the design surface.
2. Open it up and add a OLE DB source and OLEDB Command components to the design surface.
3. Modify the source to use the query you have described.
4. Connect the source to the Command component.
5. Modify command component to use "Update PreorderStatus set StatusID = 2 where ProductID = ?" query and on param mapping page map the ? variable to the input coming from the datasource.

HTH