How to insert extracted rows into another table using ssis

Question

I have a table and it has 500 rows. I want to get only 10 rows, and I want to insert them into another table using only the control flow. Through the data flow task, we can use the OLEDB source and destination OLEDB. But I want to get the result in such a way that using the sql task and for each cycle. Can this be done? My idea is to get a set of ten records and, using the foreach loop, iterate over each row and insert into the table using the sql task. The destination table must be created on the fly. I tried with some approach, but did not move. Please find the image file.

Answer 1

Example taken from Northwind

Create variables (in a collection of variables) that represent the columns in the table that will be created at runtime Example: -

Customer_ID as string Order_Id as int 

Then you need to create an Execute SQL Task and write a query below to select the first 10 rows

  Select top 10* from orders 

Use FullResultSet and in the result set configuration save the table rows in the variable Name: - User :: Result ResultName: 0

Drop one SQL execution task and create a table on the fly

  IF OBJECT_ID('myOrders') IS not NULL drop table myOrders Create table myOrders (OrderID int, CustomerID varchar(50) ) 

combine 2 threads from an Execute sql task and connect them to the Foreach loop

Drag the foreach loop. As an enumerator type, it is used as an ADO ADACHER enumerator. In the counter configuration, select the user :: Result variable, which stores the first 10 rows from the sql execution task and selects the "Rows in the first table" radio button. In the variable display, compare the column variables that u created on the first step, and the index will be 0 for the first column and 1 for the second column

Drag the sql execution task inside the foreach loop and write the following query:

  Insert into myOrders( OrderID,CustomerID) values (?,?) 

Match parameters using parameter mapping configuration in sql task execution

  VariableName : OrderID Direction : Input DataType=Long ParamterName=0 VariableName : CustomerID Direction : Input DataType=varchar ParamterName=1 

Answer 2

I hope you do this in a "learning mode". There is no reason why this should be done with a control flow over a data stream.

Anyway, your print screen is correct, I would just add another sql execute task at the beginning to create a destination table.

Then your sql execution task should have a query to cast the ten rows you want, its result set should be set to “Full result set”, and on the results tab, you should map the result set to a variable as follows:

and configure the foreach loop container as follows:

in each foreach loop you will have access to the values ​​of the variables, then you can use another sql execute task to insert then into a new packed table