How to insert retrieved rows into another table using ssis

Question

I have a table and it has 500 rows. I want to retrieve only 10 rows and i want to insert into another table using control flow only. Through data flow task we can use OLEDB source and OLEDB destination. But i want result in such a way that by using execute sql task and for each loop. Is it possible to do in that way? My Idea is, get the set of ten records and and by using foreach loop iterate to every row and insert into the table by using execute sql task. The destination table need to create on the fly. I tried with some approach but not moving towards. Please find the image file.

Answer 1

Example taken from Northwind

Create variables (in variable collection) which represent the columns in the table which u ll create at runtime Example :-

       Customer_ID as string
       Order_Id as int   

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

   Select top 10* from orders

Use FullResultSet and in Result Set configuration store the table rows in a variableName :- User::Result ResultName:0

Drop one Execute SQL Task and create a table on fly

 IF OBJECT_ID('myOrders') IS not NULL
 drop table myOrders

 Create table myOrders
 (OrderID  int,
  CustomerID varchar(50)
 )

combine the 2 flows from Execute sql task and connect it to the Foreach loop

Drag a foreach loop .In collection use enumerator type as Foreach ADO Enumerator In enumerator configuration select user::Result variable which stores the top 10 rows from the execute sql task and select the radio button " Rows in the first table" In variable mapping ,map the column variables which u have created in the first step and the index will 0 for first column and 1 for 2nd column

Drag a execute sql task inside a foreach loop and write the below query :

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

Map the parameters using parameter mapping configuration in execute sql task

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

Answer 2

I hope you are doing this on a "study-mode". There is no reason why to do this on the control flow over the data flow.

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

Then, your execute sql task should have the query to bring the 10 rows you want, its result set should be set to "Full result set" and on the resultset tab you should map the result set to a variable like this:

and configure your foreach loop container like this:

on each loop of the foreach you will have access to the values on the variables, then you can use another execute sql task to insert then on the new crated table