How to insert retrieved rows into another table using ssis

5
votes

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.enter image description here

2
sql-serverssis
You want just get 10 rows from 500 rows and insert into another on fly created table ? – Sudhakar B
Destination table is in SQL server or it is through OLEDB ? – Sudhakar B
Destination table we have to create on the fly. It should be in sql server. It is better to keep in different database. – Searcher
Strange, why it is -1, is it not a valid question or you dont know the answer.. :) – Searcher
I dont know much on SISS, but you can do like this. Fetch your result set using OpenResultSet and insert into the on fly table like this. Openrowset info "msdn.microsoft.com/en-us/library/ms190312.aspx" Select TOP 10 * Into On_Fly_Table_Name From OpenRowSet('oledb-datasource') You can also add where condition to filter your resultset – Sudhakar B

2 Answers

6
votes

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 enter image description here

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
3
votes

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:

enter image description here

and configure your foreach loop container like this:

enter image description hereenter image description here

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