Loop Control within a DataflowTask in ETL

Question

Hi,
Being fairly new to SSIS and the ETL process, I was wondering if there is anyway to loop though a record set within a DataFlowTask and pass each row (deriving parameters from the row) into a Stored Procedure (the next step in the ETL phase). Once i have passed the row into the stored procedure, I want the results from each iteration to be written to a Table. Does anyone know how to do this?

Thanks.

Answer 1

Any OLEDB command transformation (which is used to execute a database command) in an SSIS dataflow is executed once per input row - which I think is the behaviour you want. More details here.

In your scenario, the minimum you would need would be:

Data Source -> OLEDB Command -> Data Target

Note that this isn't going to give great performance - it might be better to try and refactor your stored procedure to operate on the whole input set in one go (in which case you'd use an Execute SQL task in the control flow).

Answer 2

The following structure would work:

1. Create an object variable. (recordset_object)

2. Create an string variable. (record_string)

3. Create an "Execute SQL Command" in the control flow. The command should return the record set that you want to loop through.

4. In the "Execute SQL Command", in the General tab set the Result Set = Full result set.

5. In the "Execute SQL Command", in the Result Set tab set the Result Name = 0 and Variable Name = (recordset_object).

6. Create a "Foreach Loop Container" and create a precedence constraint between the "Execute SQL Command" and the "Foreach Loop Container".

7. In the "Foreach Loop Container", in the Collection tab set Enumerator = Foreach ADO Enumerator.

8. In the "Foreach Loop Container", in the Collection tab set the ADO object source variable = User::recordset_object.

9. In the "Foreach Loop Container", in the Collection tab set the Enumeration mode = Rows in the first table.

10. In the "Foreach Loop Container", in the Variable Mappings tab set teh Variable = User::record_string and the Index = 0.

11. In the "Foreach Loop Container" in the design surface of the Control Flow, add an "Execute SQL Command".

12. For the child "Execute SQL Command", you can (13) set the SQLStatement to either use a variable that generates the code you want to execute, or (14) map in a parameter, or (15) make the record_string a SQL command that is executed by the code.

13. If you use a variable, then it could be something like User::sql_code_string and its value could be something like "EXEC schema.some_stored_procedure '" + @[record_string] + "';". You would then set the SQLSourceType in the General tab of the child "Execute SQL Command" = Variable and set the SQLStatement to User::sql_code_string.

14. If you use a parameter, in the child "Execute SQL Command" in the Parameter Mapping tab set Variable Name = User::record_string, Direction = Input, Data Type = VARCHAR, Parameter Name = 0, Parameter Size = -1. In the General tab of the child "Execute SQL Command", set the SQLStatement to "EXEC schema.some_stored_procedure ?".

15. Similar to 13, but instead of creating a separate variable, you can execute User::record_string. This could work if the content of record_string that was returned by your data set is the query you want to execute.

I generally prefer this approach over @Ed's solution you can include additional steps for each record. For instance, I often add in additional objects in my Control Flow like Script Tasks, Data Flows, and Execute SQL Commands. It's a more flexible, easy to understand approach from my perspective, but @Ed's solution definately meets the criteria of your question.

Good luck and let me know if you need clarification on the instructions.