SSIS Design Pattern - Read a DataSet From Variable In a Script Task
Introduction
One of the things I appreciate about SSIS is the script elements. There is a lot of functionality built into the engine out of the box but I like the idea of having a Script Task on the Control Flow and a Script Component on the Data Flow just in case.
Last month I used a Script Task to build a more flexible FTP client class. Last week I re-used that code to perform a bunch of uploads. I had a list of files to upload in a dataset stored inside an SSIS Object variable. I could have pulled my FTP client Script Task into a ForEach loop and simply iterated the Object variable with the ADO Enumerator, but I decided against it for a couple reasons:
1. What fun would that be?
2. Seriously, I wanted to avoid the overhead of opening and closing the FTP connection once for each file. I wanted to open the connection, log in, send all the files in a list, then close the connection.
A Note About The ActiveX Script Task
Don't use it.
Why? It has "I'm not going to be around much longer" written all over it. The only time I use this task is during DTS conversion.
Use the Script Task. .Net is fun. You'll like it, I promise.
On To The Project!
Poking around the blogosphere, I found some good information and nifty code written by Jamie Thomson to get me started. Note: There are two types of SSIS developers, those who read Jamie Thomson's blog and those who will.
Follow these steps to build an SSIS project that demonstrates how to read (or shred) a dataset in an SSIS Object variable from a Script Task.
First, create a new SSIS project. I called mine "DatasetVariableInScriptDemo". When the project opens, drag an Execute SQL Task and Script Task onto the Control Flow canvas.
Double-click the Execute SQL Task to open the editor. Click the Connection property and define a new connection () to your local AdventureWorks database. (Note: If you do not have the AdventureWorks sample databases installed, you can download them .)
Set the SQLStatement property by clicking the ellipsis in the SQL Statement textbox and adding the following code to the "Enter SQL Query popup:
SELECT Top 10
ContactID
,Title
,FirstName
,LastName
FROM Person.Contact
This query returns ten rows from the Person.Contact table.
Set the ResultSet property to "Full result set".
Click "Result Set" from the list on the left to map the result set properties to variables. Click the Add button and change the NewResultName text to "0". Under Variable Name, click "" and define a new package-scoped, Object type variable named dsVar. Click the OK button to close the Add Variable dialog and create the variable.
Click the OK button to close the Execute SQL Task editor.
Connect the Execute SQL Task to the Script Task by dragging an Execute SQL Task precedence constraint (the green arrow visible when you click the Execute SQL Task) to the Script Task.
Double-click the Script Task to open the editor. Click Script from the list on the left to open the Script property page. Add dsVar to the ReadOnlyVariables property.
Click the Design Script button to open the Visual Studio for Applications editor.
Open the Project Explorer. Right-click the References logical folder and click Add Reference. Select System.XML and click the Add button, then click the OK button to add a System.XML reference.
Return to the script editor and replace the supplied code with the following:
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.Xml
Imports System.Data.OleDb
Public Class ScriptMain
Public Sub Main()
Dim oleDA As New OleDbDataAdapter
Dim dt As New DataTable
Dim col As DataColumn
Dim row As DataRow
Dim sMsg As String
oleDA.Fill(dt, Dts.Variables("dsVar").Value)
For Each row In dt.Rows
For Each col In dt.Columns
sMsg = sMsg & col.ColumnName & ": " & _
row(col.Ordinal).ToString & vbCrLf
Next
MsgBox(sMsg)
sMsg = ""
Next
Dts.TaskResult = Dts.Results.Success
End Sub
End Class
This script uses an OLEDbDataAdapter (oleDA) to fill a DataTable (dt) with the contents of the dsVar SSIS package variable, then iterates each row and column to build a string containing the data in the row. It then pops up a messagebox for each row displaying the row's contents before moving to the next row.
Your code would replace the lines that build and display the message.
Conclusion
I like the flexibility offered here. Because of this approach I was able to accomplish my goal of building and re-using a more flexible FTP client in an SSIS Script Task.
Get the code!
:{> Andy
Technorati Tags: SSIS Design Pattern Script Dataset Variable