Friday 24 July 2009

SSIS: Retrieve data from Recordset Destination

Some times when developing a SSIS package, it is handy to import data into an in-memory Recordset instead of a file or a database table.

However SSIS doesn't provide out of box support for reading data from a Recordset. A little workaround is required to make this working.

First, define a user variable of type "object".
Then map the varaible with the Recordset Destination object.



The last step is to drag a Script task onto the Control flow. Put the following code in the script and you are blessed with a DataTable object.

Dim dt As New Data.DataTable
Dim adapter As New Data.OleDb.OleDbDataAdapter
If Dts.Variables.Contains("RGRecordset") Then
adapter.Fill(dt, Dts.Variables("RGRecordset").Value)
End If

4 comments:

Hussein Yousef said...

Nice article but can you show how to use the table in the design to get the data from!

Thanks

Yan (Pamela) Yang said...

You can use a script component as the data source. The recordset can be converted into a ADO.Net table in script and from there you can define the output metadata (output columns). this will help you use the table at design time.

ME said...

Can you send a screen shot of the Script Task to create the DataTable Object or post the SSIS dtsx sample.

-Paul

sajid said...

Thanks It worked for me