question

AlAlper avatar image
AlAlper asked

SSIS Execute SP with parameters from CSV

I can not find anything on this. I have an Execute SQL Task that calls a Stored Proc with defined variables(ADO connection) that will be input as the Parameter values.

I have a CSV file that contains the data for the sp parameters as field in each row.

I need to loop through this file and execute the SP for the parameters in each row.

How do I do this the best way. I can not figure out how to do this with a for each loop but suspect this is part of the answer.

Thanks In Advance

sql-server-2005ssisstored-procedures
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
Pavel Pawlowski avatar image
Pavel Pawlowski answered

The solution is simple.

  1. Create a Packge Variable of Object Type.
  2. Create a Data Flow Task
  3. Inside the Data Flow Task put Flat File Source and create a connection to your CSV File.
  4. Inside the Data Flow Task put Record Set destination and connect it with the Flat File Source.
  5. In the Record Set destination select columns you need and assign a VariableName created in the step 1. the varialbe will represent the record set.
  6. In the Control Flow create a Foreach Loop container and connect it with the Data Flow Task.
  7. In the collection properties select Foreach ADO Enumerator as enumerator and in the configuration of the enumerator select your varible created in step 1. as ADO object source variable.
  8. Map your variables for parameter to the columns of the record set by index.
  9. Inside Forea Loop container put your Execute SQL Task and assign variables etc.

Hope it will help you.

More info on the Foreach Loop container you can find on MSDN http://msdn.microsoft.com/en-us/library/ms187670(SQL.90).aspx

10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.