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 (<New connection…>) to your local AdventureWorks database. (Note: If you do not have the AdventureWorks sample databases installed, you can download them here.)

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 “<New variable…>” 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

PS – The PASS Summit 2015 Call for Speakers closes at 9:00 PM PDT Sunday, 15 Mar 2015 (04:00 GMT 16 Mar 2015). There’s still time, but hurry!

Learn more:
Expert SSIS Training (Live, Online)
IESSIS1: Immersion Event on Learning SQL Server Integration Services (In-person)
Stairway to Biml
Stairway to Integration Services

SSIS2014DesignPatterns200

Technorati Tags:

Andy Leonard

andyleonard.blog

Christian, husband, dad, grandpa, Data Philosopher, Data Engineer, SSIS and Biml guy. I was cloud before cloud was cool. :{>

76 thoughts on “SSIS Design Pattern – Read a DataSet From Variable In a Script Task

  1. Thanks RP!
      That’s a good implementation idea! There’s so many cool uses for this functionality. Kudos to Jamie Thomson for doing the heavy lifting on this!
    :{> Andy

  2. Hi ,
    I made some changes below to the code that it will be more useful if we are talking about DataSet.
    Public Sub Main()
           Dim dt As New DataTable
           Dim col As DataColumn
           Dim row As DataRow
           Dim results As DataSet
           Dim sMsg As String
           Try
               results = CType(Dts.Variables("dsVar").Value, DataSet)
               For Each tbl As DataTable In results.Tables
                   For Each row In tbl.Rows
                       For Each col In tbl.Columns
                           sMsg = sMsg & col.ColumnName & ": " & _
                                  row(col.Ordinal).ToString & vbCrLf
                       Next
                       MsgBox(sMsg)
                       sMsg = ""
                   Next
               Next
           Catch ex As Exception
               Dts.Events.FireError(-1, "N/A", ex.ToString(), "", 0)
           End Try      
           Dts.TaskResult = Dts.Results.Success
       End Sub

  3. Hi Andy,
    Very good stuff here!  I had the issue of wanting to dynamically build a text file from a list of SQL tables…so i have a ForEach loop that passes in the table name to another SQL Task that creates a full result set into a variable.  I then use part of your code with a streamwriter to build out the text file.  
    Again, great snippit to overcome a big issue in my mind with SSIS…that being the lack of ability to change metadata being pumped to a txt file at runtime.
    Dave

  4. Hi Andy,
    When I run your code I get :
    " Object is not an ADODB.RecordSet or an ADODB.Record.
    Parameter name: adodb "
      at System.Data.OleDb.OleDbDataAdapter.FillFromADODB(Object data, Object adodb, String srcTable, Boolean multipleResults)
      at System.Data.OleDb.OleDbDataAdapter.Fill(DataTable dataTable, Object ADODBRecordSet)
      at ScriptTask_d195cb0e8fc747399ad752205c10a68b.ScriptMain.Main()
    What could be the problem?

  5. Hi Andy,
    When I run your code I get :
    " Object is not an ADODB.RecordSet or an ADODB.Record.
    Parameter name: adodb "
      at System.Data.OleDb.OleDbDataAdapter.FillFromADODB(Object data, Object adodb, String srcTable, Boolean multipleResults)
      at System.Data.OleDb.OleDbDataAdapter.Fill(DataTable dataTable, Object ADODBRecordSet)
      at ScriptTask_d195cb0e8fc747399ad752205c10a68b.ScriptMain.Main()
    What could be the problem?

  6. Hi Slava,
      I’m not sure. I haven’t seen that one before. Could you zip up your SSIS package and send it to me? andy dot leonard at gmail dot com. (Replace "dot" with "." and "at" with "@").
    Andy

  7. Andy, I used your download:), however is it possible the problem is that I need sql2005 Enterprise, I have 2005 Standart Server?

  8. Kenan,
    Based on a past experience your code would work if an Execute Sql task used an ADO.NET connection manager, but when you use OLEDB the type of object in the variable is not a DataSet.  It is instead an ADODB Recordset which is why we need the OleDbDataAdapter to do the fill on it.

  9. Hello Andy,
    I’m sort of a rookie with this stuff, but I found your code helpful to the point that I know I’m getting the data I want, I’m pulling email addresses from my SQL task, and I want to send a email to those addresses, but it seems that no matter what I try, I can not use the results from the sql query to populate the Send to or BCC fields, I always end up getting an error that reads conversion from data type Object to String is not permitted, and if I attempt to not convert the results from an object to a string I get a different error, AND if I change my variable from object to string, I don’t get any results! Have you ever tried something like this before?

  10. Hi Scott,
      I have done similar things before. I would not use this SSIS pattern to accomplish it.
      The approach I would take with SSIS is as follows:
    1. Collect the email dataset into an SSIS Object variable as described above.
    2. Add a ForEach Loop Container configured to use the ADO Enumerator to pump values from the dataset into variables. This will loop through the rows in the dataset and set variables (maybe EmailSendTo and EmailBCC variables, for example) for each iteration of the loop – and it will iterate once for each row in the dataset.
    3. You could then place a SendMail Task inside the ForEach Loop Container.
      I wrote an article about ForEach Loops for SQL Server Central recently. The editor tells me it should be up in early August.
    Hope this helps,
    Andy

  11. Aha, this is exactly what I was looking for! OleDbDataAdapter, that was the answer…  Darn, I spent so much time trying to unsuccessfully coerce that Object thing into one of the Collection types because I saw a code snippet using CType(Object, Hashtable).
    Superb!  Thank you so much, Andy!!!

  12. I had the same issue as Slava but found that I didn’t have the object variable (dsVar) set up correctly as an object.  School Boy error 🙁  Hope this helps.

  13. Hi Andy,
    I have implemented your code in one of my SSIS package but i want to assign the output to SSIS variable:
    I ran the below code but this gives and Object referenece error.
    Dim olead As New Data.OleDb.OleDbDataAdapter
           Dim dt As New Data.DataTable
           Dim row As System.Data.DataRow
           Dim col As System.Data.DataColumn
           Dim str As String
           olead.Fill(dt, Dts.Variables("varResult3").Value)
           For Each row In dt.Rows
               For Each col In dt.Columns
                   str = str & row(col.Ordinal).ToString() & ","
               Next
           Next
           Dts.Variables("varMeeting_Time").Value = str.Remove(str.LastIndexOf(","), 1)
    How can I solve this?

  14. I tried the method mentioned by kenan to convert the object variable to dataset and then loop through the data,but i got type cast error saying:object data type can not be casted to dataset.
    Please help on how to proceed with this.

  15. Ajay,
    I got the conversion error when using the OleDb connector. I changed to use an ADO.NET connector and the error went away.
    Cliff

  16. What if i wanted to modify the datatable and then write it to a variable, for read in future tasks? any help would be appreciated

  17. Andy in your comment from October 29, 2007 6:30 AM; you apologize about not mentioning the XML reference; but in you screen shot the XML reference is there and you are receiving an error stating you need a reference to the Serialization.IXMLSerial.
    I am getting the same reference error and I have the System.XML reference; how did you solve this issue in your package?

  18. Nice article.helped me a lot. ACtually i need to retreive a table based on some conditions and then modify the dataset and put it back to the databse.Can anybody please tell me how to return the same data set back to database

  19. Had same problem as Salva.  Ends up my Execute SQL Task Result Set was set to XML.  changed it to Full Result set and it doesn’t error now.

  20. hi
    I have a requirement to loop the below logic (Same as given above) but..there is a problem..! I am not finding any data within the Object variable "dsVar" when I try to read it for the second time. Is Reading data using below logic ‘Destructive’ ?
    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

  21. To put my problem in a simpler way ..so far we have seen
    Populate dsVar (SQL Task)
     |
    Shred dsVar (Srcipt Component)
    It displays all Rows.
    Now I am trying….
    Populate dsVar (SQL Task)
     |
    Shred dsVar (Srcipt Component) – Displays all rows
     |
    Shred dsVar 1 (Srcipt Component) – Copy paste above script.
    when the second script component runs it will not Display any Rows as there is no data in dsVar.
    Can any one help me to retain the data in dsVar object variable.
    Regards, Raj

  22. Is this something that only works in SSIS 2008?
    In SSIS 2005 I get the message: "Only DTS objects of type ForEachEnumerator can be assigned to variables. Other DTS objects are not allowed in variables."
    Thoughts?

  23. HI !
    I m new to ssis script task please anyone help me to insert the same object variable to anoter table in above code

  24. DataSet ds = new DataSet();
    OleDbDataAdapter oda = new OleDbDataAdapter();
    ds = (DataSet)Dts.Variables["RecordsetOutput"].Value;
    While using the above code to fetch data from the Dts.Variables to dataset i am getting the error "System.InvalidCastException: Unable to cast COM object of type ‘System.__ComObject’ to class type ‘System.Data.DataSet’. Instances of types that represent COM components cannot be cast to types that do not represent COM components; however they can be cast to interfaces as long as the underlying COM component supports QueryInterface calls for the IID of the interface."
    Can anyone help me out on this ..I am writing in C#

  25. When attempting to cast from variable value to a DataSet object as in Kenan’s modified code, I get a cast exception error. Any thoughts?
    Thanks,
    Jason

  26. Dude you is smart.. this was making me angry. Kept trying to CType right to a DataTable & getting the finger. Not sure how you figured this one out but well done. Thanks for sharing!

  27. Hi Andy,
    Please help me on creating SSIS package to acomplish the below requirment.
    read the file names from one txt file and search in one directory, if any single file is not found it has to wait for 10 sec. In case of all files found in the folder convert all files into .bak and exit from the package.

  28. @deepika, for C# I successfully use following:
    OleDbDataAdapter l_oleDA = new OleDbDataAdapter();
    System.Data.DataTable l_dt = new DataTable();
    l_oleDA.Fill(l_dt, ReadOnlyVariables["MyRecordsetVariableAsObject"].Value);
    if (l_dt.Rows.Count > 0)
    {
     foreach (System.Data.DataRow l_Row in l_dt.Rows)
       {
         …etc.

  29. C# version, if anyone still needs it:
    DataTable DT = new DataTable();
    OleDbDataAdapter DA = new OleDbDataAdapter();
    DA.Fill(DT, Dts.Variables["FileTypes"].Value);
    foreach (DataRow DR in DT.Rows)
    {
    }

  30. You said you worked on flexible FTP client class.
    Can you provide me help on that, as I am working on something that is on similar lines.

  31. Can any one help me ..!!
    How i acess multiple result sets from object variable in ssis
    example:
    My Store Procedure is having like this
    Create Proc SP1
    as
    selct * from table1
    selct * from table2
    selct * from table3
    exec SP1
    Result of SP is stored in Object variable in Execte sql statement
    Now i need to access that varible in script task and i need to keep each table result in individual text file
    and i need to transfer that file into ftp server

  32. Hi Andy,
    I’m having this error when I ran the code above.
    What could be the problem?
    Object is not an ADODB.RecordSet or an ADODB.Record.

  33. Hi I am using below code, but getting gap between each line which I don’t want. tried removing one of VbCrLf but it resulting in same row.Any help would be appreciated.
    Dim oleDA As New OleDbDataAdapter
           Dim dt As New DataTable
           Dim col As DataColumn
           Dim row As DataRow
           Dim sMsg As String
           Dim pos As Integer = 0
           sMsg = ""
           oleDA.Fill(dt, Dts.Variables("vrsTableList").Value)
           For Each row In dt.Rows
               pos = 0
               For Each col In dt.Columns
                   If pos = 0 Then
                       sMsg = sMsg & col.ColumnName & ": " & row(col.Ordinal).ToString & " – "
                   Else
                       sMsg = sMsg & col.ColumnName & ": " & row(col.Ordinal).ToString & vbCrLf
                   End If
                   pos = pos + 1
               Next
               ‘sMsg = sMsg & vbCrLf
           Next
           Dts.Variables("vDimOutputMsg").Value = sMsg

  34. Please can you explain how to perform incremental uploads to multiple tables.  All examples are always based on a single source to destination table

  35. Hi Tee,
      I recommend writing one incremental load data flow task per table, and I advocate using as few data flow tasks per package as possible – optimally, one data flow per package (which translates into one package per table).
      You can learn more about the Incremental Load Design Pattern at the Stairway to Integration Services (http://www.sqlservercentral.com/stairway/72494/). You may want to consider Business Intelligence Markup Language (Biml) if you have several tables you wish to load incrementally. You can learn more about Biml at the Stairway to Biml (http://www.sqlservercentral.com/stairway/100550/).
    Hope this helps,
    Andy

  36. Hi Andy,
    My scenarios is to do an initial ETL of 30 odd tables in one package.
    The second package is to do an incremental upload – insert, update, delete of the 30 odd tables.
    How can I accomplish any of these using variables and For each loop so that it is quicker.
    I am using SSIS 2008.
    Thanks

  37. Hi Tee,
      One way to accomplish this is covered in these three articles – part of the Stairway to Integration Services I linked above:
    1. Adding Rows in Incremental Loads – Level 3 of the Stairway to Integration Services (http://www.sqlservercentral.com/articles/Integration+Services+(SSIS)/75331/).
    2. Updating Rows in Incremental Loads – Level 4 of the Stairway to Integration Services (http://www.sqlservercentral.com/articles/Stairway+Series/76390/).
    3. Deleting Rows in Incremental Loads – Level 5 of the Stairway to Integration Services (http://www.sqlservercentral.com/articles/Integration+Services+(SSIS)/76395/).
      It’s possible to automate the creation of SSIS packages that follow a repeatable pattern – like the Incremental Load pattern provided in the previous paragraph – using Business Intelligence Markup Language (Biml). The articles that specify how to use BIDSHelper (http://bidshelper.codeplex.com) – a free tool – to accomplish automating the build of an incrementally-loading SSIS package for every table in a database can be found in these articles:
    1. Biml Basics – Level 2 (http://www.sqlservercentral.com/articles/Stairway+Series/100585/).
    2. Building an Incremental Load Package – Level 3 (http://www.sqlservercentral.com/articles/BIML/101330/).
    3. Using Biml as an SSIS Design Patterns Engine – Level 4 (http://www.sqlservercentral.com/articles/BIML/101472/).
      Read those six articles. They provide the examples you seek.
    Andy

  38. Hi Andy,
    My scenarios is to do an initial ETL of 30 odd tables in one package.
    The second package is to do an incremental upload – insert, update, delete of the 30 odd tables.
    How can I accomplish any of these using variables and For each loop so that it is quicker.
    I am using SSIS 2008.
    Thanks

  39. Hi Tee,
      Unless the 30 tables are identical in format, you cannot use the same data flow to load them. Are they identical?
    Andy

  40. Hi Andy,
    Im trying to replicate your example on shredding a dataset and passing multiple values to a sql query in DFT ,
    eg : TABLE A  AREACODE (1,2,3,4)
        TABLE B  Customer
    select * from customer where areacode in (select distinct areacode from areacode) . Can you give me some direction please

  41. Hi Andy,
    I am looking for the reverse case, like pass excel file with multiple sheet with different column structure to scripttask then it will return dataset and pass to sql task to insert in SQL database.
    Please suggest how we can achieve this.
    Thanks in advance.
    Sanjeev Gupta

  42. this is usefull .. but  I need to populate Excel spreadheet from datatable
    I would like not to loop 14000 but assign value at once using CopyFromREcodset .. Is that possible ??
    thank upi

  43. Hi,
    Today I am facing an issue in re-using a Record Set destination object inside a for each loop container.
    Below is the design of my SSIS package.
    1) Inside a Data Flow Task I am calling one Stored procedure and the result is stored in a Record Set destination (User :StrResultSet)
    2)Inside an Execute SQL task I am getting distinct email address from a table and storing that in an user variable (strEmailAddress)
    3)Inside a For Each loop container I am looping through each email address sroed in strEmailAddress variable and a Scrip task is placed inside that For Each loop container to send email to the email address received from strEmailAddress and the email should have the content from  StrResultSet.
    Issue:
    For the first time when the for each loop container runs it sends an email to the appropriate email address.
    But for the second loop it fails because StrResultSet does not have any data now as it is used by the first execution. How do I re-use the data stored in StrResultSet ? Can some one help? Thanks

  44. I solved this issue -Posting the solution as it might help someone!
    Inside the For Each loop container , I populated the user object (User :StrResultSet) again from the respective datasource. So when the record set destination gets cleared off in the first loop execution it will again populate the (User :StrResultSet) for the next execution .On the whole I am just cloning the dataset before every execution.
    Hope this helps!!
    Thanks
    Nisha Venkatakrishnan

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.