THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Andy Leonard

Andy Leonard is an author and engineer who enjoys building and automating data integration solutions. Andy is co-host of the Data Driven podcast. Andy is no longer updating this blog. His current blog is

SSIS Design Pattern - Read a DataSet From Variable In a Script Task

This blog has moved! You can find this content at the following new location:

Published Sunday, October 14, 2007 6:29 AM by andyleonard

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS



Darren Harvey said:

An absolute gem of a snippet of code

many thanks

October 24, 2007 7:09 AM

Hery said:

Why we have to add System.XML when we want to use DataSet ? I found no explanation on this article. Thanks.

October 25, 2007 1:20 AM

andyleonard said:

Hi Hery,

  Apologies for neglecting to explain the need for a reference to System.XML. The DataTable object is part of the System.XML namespace. You can see a screenshot of the script without the reference at

:{> Andy

October 29, 2007 6:30 AM

Alex said:

Damn Andy, you're on the cover my SSIS book.

November 9, 2007 4:20 PM

andyleonard said:


  That picture adds functionality to the book. You can use the cover to keep the crows out of the corn!

:{> Andy

November 10, 2007 4:55 AM

sandeep said:

thanks andy.

really useful



November 30, 2007 12:52 PM

RP said:

Really good andy...  I've just created an e-mail notification SSIS using the above... Thanks.

January 22, 2008 8:10 PM

andyleonard said:

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

January 23, 2008 9:34 AM

Kenan said:

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


           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



                   sMsg = ""



       Catch ex As Exception

           Dts.Events.FireError(-1, "N/A", ex.ToString(), "", 0)

       End Try      

       Dts.TaskResult = Dts.Results.Success

   End Sub

February 6, 2008 7:07 AM

andyleonard said:

Hi Kenan,

  Very elegant coding - thanks!

:{> Andy

February 6, 2008 8:04 AM

Dave said:

Hi Andy,

Very good stuff here!  I had the issue of wanting to dynamically build a text file from a list of SQL 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.


February 8, 2008 7:59 AM

Ashish Sinha said:

Your code was a great help to dynamically display the rows, which didn't adhere to the condition.

February 15, 2008 11:26 AM

Slava said:

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?

April 25, 2008 10:03 AM

andyleonard said:

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 "@").


April 25, 2008 10:46 AM

slava said:

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

April 25, 2008 12:08 PM

andyleonard said:

Hi Slava,

  That could be it. I rarely use Standard these days - I like Developer!

:{> Andy

April 25, 2008 12:13 PM

deloveh said:


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.

May 15, 2008 1:38 PM

Scott Marchione said:

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?

July 29, 2008 11:48 AM

andyleonard said:

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,


July 29, 2008 12:21 PM

Rafael Haroutunian said:

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!!!

August 10, 2008 6:07 AM

andyleonard said:

Hi Rafael,

  I had the same experience - Jamie Thomson deserves the credit for blogging about that first ( Thanks Jamie!

:{> Andy

August 10, 2008 10:05 AM

Mark FInk said:

I received the same error that Slava did what was the resolution to it.

November 7, 2008 9:25 AM

sportrx said:


I think I should look here first before anywhere else for solution.

January 20, 2009 3:05 PM

Bkube said:

Hi all,

I ALSO am having the issue that Slava and Mark Fink referenced... is there a resolution or solution?

January 22, 2009 12:19 PM

Jan Peake said:

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.

March 6, 2009 8:31 AM

VBJ said:

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() & ","



       Dts.Variables("varMeeting_Time").Value = str.Remove(str.LastIndexOf(","), 1)

How can I solve this?

July 21, 2009 4:09 PM

Ajay said:

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.

July 22, 2009 9:19 AM

Cliff said:


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


July 28, 2009 9:59 AM

Pete said:

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

August 26, 2009 6:03 PM

Ahmed said:

how can I return a XML datatype results

November 8, 2009 7:36 AM

Sospixs said:

This is a greate article

It's useful.

Thank you

December 24, 2009 5:08 AM

Bryan said:

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?

January 11, 2010 12:32 PM

Pooja said:

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

January 20, 2010 4:04 AM

Alvin said:

Nice one.

Thank you.

February 11, 2010 1:26 AM

Srinath Chowdary said:

Fnatastic article I have ever seen on SSIS

April 17, 2010 10:12 AM

Guitarzan8 said:

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.

June 14, 2010 1:46 PM

Rajesh said:


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



  sMsg = ""


 Dts.TaskResult = Dts.Results.Success

End Sub

End Class

August 4, 2010 9:41 AM

Rajesh said:

To put my problem in a simpler way 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

August 4, 2010 11:06 AM

Saurabh said:

Rajesh, did you ever find a sol to your prob. How to retain data in Object var

November 5, 2010 7:11 PM

Joseph Williams said:

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."


May 5, 2011 2:09 PM

Prashant said:

Slava : Have you done Variable Mapping for columns in For Each Loop task

May 12, 2011 9:38 AM

Sugan said:

HI !

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

June 27, 2011 1:21 AM

deepika said:

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#

November 16, 2011 7:24 AM

Jason said:

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?



March 29, 2012 3:41 PM

Dan said:

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!

April 26, 2012 5:43 PM

Lhay said:

IS there a C# equivalent code for this please. I'm learning C# these days but already know to code in VB

July 20, 2012 7:58 PM

andyleonard said:

Hi Lhay,

  I do not have a C# equivalent for this code, sorry.


July 20, 2012 9:57 PM

Nath said:

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.

July 24, 2012 6:52 AM

Prabhu said:

Thanks, nice article.,

September 13, 2012 7:36 AM

Guillaume said:

Thank you for this very usefull script !

October 11, 2012 8:50 AM

Dimitrij Litau said:

@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)



October 17, 2012 2:39 AM

Sam Vella said:

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)



February 7, 2013 7:35 AM

Sam Vella said:

I should read comments to the end

Apologies Dimitrij for reiterating what you had already posted

February 7, 2013 7:36 AM

andyleonard said:

Sam, two helpful answers are better than zero! Thank you and Dimitrij for your responses!


February 7, 2013 8:00 AM

Rajiv said:

Nice Article

February 27, 2013 5:57 AM

G Parmar said:

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.

April 25, 2013 1:58 AM

Raghav said:


July 18, 2013 5:13 PM

Srinivas said:

Can any one help me ..!!

How i acess multiple result sets from object variable in ssis


My Store Procedure is having like this

Create Proc SP1


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

August 19, 2013 11:14 AM

cherriesh said:

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.

August 22, 2013 4:38 PM

lucky said:

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 & " - "


                   sMsg = sMsg & col.ColumnName & ": " & row(col.Ordinal).ToString & vbCrLf

               End If

               pos = pos + 1


           'sMsg = sMsg & vbCrLf


       Dts.Variables("vDimOutputMsg").Value = sMsg

September 26, 2013 3:39 PM

tee said:

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

February 17, 2014 3:09 PM

andyleonard said:

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 ( 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 (

Hope this helps,


February 17, 2014 5:01 PM

Tee said:

Thanks  for your quick response Andy.  Can you do an example of what you have explained? It would be very helpfull indeed.

February 18, 2014 3:58 PM

andyleonard said:

Hi Tee,

  The links in my response are loaded with examples.


February 18, 2014 4:23 PM

Tee said:

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.


February 18, 2014 4:55 PM

andyleonard said:

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 (

2. Updating Rows in Incremental Loads – Level 4 of the Stairway to Integration Services (

3. Deleting Rows in Incremental Loads – Level 5 of the Stairway to Integration Services (

  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 ( - 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 (

2. Building an Incremental Load Package – Level 3 (

3. Using Biml as an SSIS Design Patterns Engine – Level 4 (

  Read those six articles. They provide the examples you seek.


February 18, 2014 5:20 PM

Tee said:

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.


February 19, 2014 1:06 AM

andyleonard said:

Hi Tee,

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


February 19, 2014 10:40 AM

Jampa said:

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

March 6, 2014 4:22 PM

SanjeevG said:

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

December 24, 2014 3:10 PM

Sham said:

Hi Andy,

Thank you, Searching for this code. Very useful

February 23, 2015 4:28 AM

julia said:

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

June 16, 2015 5:06 PM

Prithivirajan said:

can u provide an script for c#

July 31, 2015 2:28 PM

Nisha V Krishnan said:


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.


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

October 13, 2015 1:17 PM

Nisha V Krishnan said:

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!!


Nisha Venkatakrishnan

October 13, 2015 2:52 PM

Leave a Comment


This Blog



My Latest Book:

Community Awards

Friend of Red Gate

Contact Me


Privacy Statement