UPDATE 2006-03-14: This information in this post should now be disregarded. Read here: http://blogs.conchango.com/jamiethomson/archive/2006/01/04/2540.aspx instead!
It is possible to populate an SSIS variable of type "Object" with a recordset using the "Recordset Destination" data flow destination component. Thereafter there are various ways to access the contents of this recordset, one of which is to use a Foreach loop to loop over the recordset (using the "For each ADO Enumerator") and then use a Script Task to access the enumerated record.
The record can be exposed in the script task as an ADODB.Fields object. The following code shows how to access the various values in the record:
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Public Class ScriptMain
Public Sub Main()
Dim vars As Variables
Dim fields As ADODB.Fields
Dts.VariableDispenser.LockOneForRead("vRecord", vars)
fields = CType(vars(0).Value, ADODB.Fields)
MsgBox(fields(0).Value.ToString() + " - " + fields(1).Value.ToString())
Dts.TaskResult = Dts.Results.Success
End Sub
End Class
HOWEVER...there is a caveat here. The ADODB namespace cannot be accessed straightaway using the off-the-shelf installation. Follow these steps in order to make the ADODB namespace accessibe to your scripts:
1.. Copy %Program Files%\Microsoft SQL Server\90\Tools\bin\adodb.dll to %windir%\Microsoft.net\framework\v2.0.xxxxx
2.. In the script task script editor (Visual Studio for Applications) go Projects-->Add Reference and add adodb
Personally I think this namespace should be accessible without all this messing about, it should be installed into the correct location. So to the SSIS dev team....can the installation be changed to install this DLL (and maybe many others) into the correct place in order for it to be used?
- Jamie
N.B. I've got a demo package for all of this but we're having problems with our blog site at the mo which means I can't upload files at the moment. If you want the demo package sending to you post a comment up here with your email address and I'll send it to you!
UPDATE:
Kirk says there is functionality coming in IDW14 that means all of this messing about with DLLs won't be necassary. Great news! Read about it here!