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:
Nice article but can you show how to use the table in the design to get the data from!
Thanks
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.
Can you send a screen shot of the Script Task to create the DataTable Object or post the SSIS dtsx sample.
-Paul
Thanks It worked for me
Post a Comment