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
at

4 comments:

  1. Hussein Yousef23 March 2011 at 05:51

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

    Thanks

    ReplyDelete
  2. Yan (Pamela) Yang29 March 2011 at 16:13

    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.

    ReplyDelete
  3. ME17 December 2011 at 08:02

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

    -Paul

    ReplyDelete
  4. sajid14 September 2012 at 05:32

    Thanks It worked for me

    ReplyDelete