Using SSIS Execute SQL Task Results in a Downstream Script Task
This post contains some information about how you can access the results of an SSIS Execute SQL task in a downstream Script task.
First, it's important to use an ADO.NET connection instead of an OLE-DB connection with the Execute SQL task because with an OLE-DB connection the results will *not* be available in a downstream script task. This is because the results variable will store a COM object, which will be (unless there's a trick I don't know about) will be unavailable in the script task. If you instead use an ADO.NET connection for the Execute SQL task, the Object variable that stores the results will hold an ADO.NET 2.0 DataSet object, which is easily accessible from a Script task.
In addition, as shown in this similar example by Kirk Haselden, you have to use "0" for the "Results" name in the Execute SQL dialog, mapping it to an Object type variable.
Notice in this code example (which is from a script task that is downstream from an Execute SQL task that is storing its results in an Object variable called MyAdoVariableName) how we're using System.Data.DataSet as our data type. (Note: the example uses my custom ReadVariable() and WriteVariable() functions.)
Public Sub Main()
'Assume success
Dts.TaskResult = Dts.Results.Success
Try
Dim results As DataSet
results = CType(ReadVariable("MyAdoVariableName"), DataSet)
For Each tbl As DataTable In results.Tables
System.Windows.Forms.MessageBox.Show(tbl.TableName)
Next
Catch ex As Exception
Dts.Events.FireError(-1, "N/A", ex.ToString(), "", 0)
End Try
End Sub
Since you have an ADO.NET 2.0 DataSet, you can also get a DataTableReader for it:
Dim reader As DataTableReader
reader = results.Tables(0).CreateDataReader()
Don't forget to use Finally or Using blocks to ensure that your DataSet and DataTableReader objects clean up their resources.
Note: to get all this to work I also had to add a reference to System.Xml.dll to the Script Task. A nicely worded error message will nag you about it if you forget. You should find System.Xml.dll in the normal list that comes up when you click Add Reference.
Hope that helps. Comments welcome.
Dan
ReadVariable?
Hello,
I get an error on ReadVariable? Where does that function come from?
Any help would be appreciated.
Thanks,
Todd
Home for ReadVariable() Function
Hi Todd,
Sorry about that, I used that in my code example without pointing to the definition for the function. My custom ReadVariable() and WriteVariable() functions for SSIS can be found here. I'll update the original post accordingly.
Thanks,
Dan
Re-writing SQL 2000 DTS for SQL 2005 SSIS
My organization recently upgraded to SQL Server 2005. I have several 2000 DTS packages which I imported into 2005 SSIS. I noticed that some data transformation tasks are still stored in 2000 format, and are not always reliable. I want to re-write all the 2000 tasks into 2005. I have one particular task which is driving me crazy. I capture data from a view using a SQL command as the source, transform 1 field into a full description using vbScript, then write the transformed data into an Excel file as the destination. The code, which worked fine in 2000, was:
' Copy each source column to the destination column
Function Main()
dim vEssenCode, vEssenDesc
vEssenCode = DTSSource("Essential_Code")
If vEssenCode = "E" Then
vEssenDesc = "EOC - Able to Perform"
Elseif vEssenCode = "D" Then
vEssenDesc = "Dept - Able To Perform"
ElseIf vEssenCode = "1" Then
vEssenDesc = "EOC - Not Able To Perform"
ElseIf vEssenCode = "2" Then
vEssenDesc = "Dept - Not Able To Perform"
Else vEssenDesc = "Non-Applicable"
End If
DTSDestination("Essen_Code") = vEssenDesc
Main = DTSTransformStat_OK
End Function
I read your posting on 'Using SSIS Execute SQL Task Results in a Downstream Script Task', but I haven't been able to apply it to my task. I know how to write the script. I just can't figure out how to get those fields, feed their values into variables, and process them in the script. Do you have any suggestions?
COM Objects
Hiya Daniel,
Great post. Just to let you know that I *think* the code here allows you to access data in a COM object.
-Jamie