Need help, please :-)

[katbear]

I wonder if you have a moment to answer a question. Basically, I am in an SSIS crash course! I have been using your book quite a bit, but it does not address the problem I am trying to solve, which is probably quite straightforward.

Basically, all I am trying to do is create an SSIS pkg that will read a list of stored procedures from a table. I set up an EXECUTE SQL TASK to do this. I am saving the result set to a variable (type object).

Next, I call a FOREACHLOOP container, which references the variable from the EXECUTE SQL TASK.

But I'm not sure what to do next?? I just want the FOR EACH LOOP to simply execute the stored procedure name as given by the first EXECUTE SQL TASK.

I was thinking to put another EXECUTE SQL TASK inside the FOR EACH LOOP, but I don't know how to reference the stored procedure name correctly, or if this is even allowable.

I am not a scripter

Can you please offer me some advice on what to do next?? I am desperate.

 

[paulkeys]

What i would do... (others may advocate a different approach)

1. (Think you've already done this step) Set up a dataflow task with recordset destination that populates a object variable
2. Within the For Each Loop, Collection Tab, select Foreach ADO Enumerator as the enumerator, and set the source variable to be your object variable
3. In the variable mappings tab, map the relevant table entries to a user variable e.g. SP_Name (type - string)
4. Add an execute sql task within the foreach loop, relevant connection manager (presuming they're all in same db). SQL would be 'exec ?' then within the variable mappings tab, add in the variable (SP_Name in this example) that holds the name of the stored procedure.

The For Each will then receive the recordset from the dataflow task via an object variable, and for each record will assign the stored proc name to a variable, which will then be used within the exec sql task.

Hope this is clear - let me know if you need any more help...