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


Hussein Yousef said...

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


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.


sajid said...

Thanks It worked for me