Looping through multiple rows in SSIS package

Question

I have a search query which will return multiple rows and columns. I have to create Insert query according to the columns returned from search query. How can I loop to each and every row returned and use the columns of the same row.

I want to do all the operations in data flow task. I'll have a OLE DB destination or OLE DB command to run the insert query.

With the limited information provided, I would say become familiar with the while loop. To get a better answer you will need to provide more details such as a code snippet, what you've tried already, and what you're expecting as an output. — gbeaven
– gbeaven, Commented Sep 7, 2018 at 15:16
I'm sorry for that, what currently I'm trying to do is. I have an OLE DB source which will run the SELECT statement and return 5 columns. I have to create INSERT statement using 2 columns and there can be multiple rows returned. I want to loop trough each of them. — Sameer
– Sameer, Commented Sep 7, 2018 at 15:26
Check out the link I provided above. It contains documentation for the while loop in SQL. — gbeaven
– gbeaven, Commented Sep 7, 2018 at 15:29
Is there a way we can do this without editing the query. Maybe something from the SSIS toolbox which can loop through all the columns present. I'm really new to SSIS packages. — Sameer
– Sameer, Commented Sep 7, 2018 at 15:48

Jeremy J. · Accepted Answer · 2018-09-10 18:45:07Z

1

You have a basic misunderstanding of how the data flow task works. You do not need to generate insert statements in a row by row fashion. Use an OLE DB Destination. You must choose (and configure) your connection manager, choose the destination table, and map the columns from the source to the destination, but SSIS handles inserting the data to the destination table.

You mention having five source columns and two destination columns, so you may need to do some transformations between the OLE DB source and the OLE DB destination. That where you may use some of the transformations in the SSIS Toolbox like Derived Column or Lookup.

answered Sep 10, 2018 at 18:45

Jeremy J.

7274 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Looping through multiple rows in SSIS package

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related