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
Technorati Tags: SSIS Design Pattern Script Dataset Variable
An absolute gem of a snippet of code
many thanks
Why we have to add System.XML when we want to use DataSet ? I found no explanation on this article. Thanks.
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 http://andyleonardconsulting.com/images/ext/SystemXML.png.
:{> Andy
Damn Andy, you’re on the cover my SSIS book.
Alex,
That picture adds functionality to the book. You can use the cover to keep the crows out of the corn!
:{> Andy
thanks andy.
really useful
🙂
cheers
Really good andy… I’ve just created an e-mail notification SSIS using the above… Thanks.
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
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
Hi Kenan,
Very elegant coding – thanks!
:{> Andy
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
Your code was a great help to dynamically display the rows, which didn’t adhere to the condition.
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?
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?
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
Andy, I used your download:), however is it possible the problem is that I need sql2005 Enterprise, I have 2005 Standart Server?
Hi Slava,
That could be it. I rarely use Standard these days – I like Developer!
:{> Andy
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.
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?
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
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!!!
Hi Rafael,
I had the same experience – Jamie Thomson deserves the credit for blogging about that first (http://blogs.conchango.com/jamiethomson/archive/2006/01/04/2540.aspx). Thanks Jamie!
:{> Andy
I received the same error that Slava did what was the resolution to it.
thanks
I think I should look here first before anywhere else for solution.
Hi all,
I ALSO am having the issue that Slava and Mark Fink referenced… is there a resolution or solution?
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.
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?
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.
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
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
how can I return a XML datatype results
This is a greate article
It’s useful.
Thank you
http://mssqlbase.blogspot.com
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?
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
Nice one.
Thank you.
Fnatastic article I have ever seen on SSIS
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.
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
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
Rajesh, did you ever find a sol to your prob. How to retain data in Object var
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?
Slava : Have you done Variable Mapping for columns in For Each Loop task
HI !
I m new to ssis script task please anyone help me to insert the same object variable to anoter table in above code
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#
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
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!
IS there a C# equivalent code for this please. I’m learning C# these days but already know to code in VB
Hi Lhay,
I do not have a C# equivalent for this code, sorry.
Andy
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.
Thanks, nice article.,
Thank you for this very usefull script !
@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.
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)
{
}
I should read comments to the end
Apologies Dimitrij for reiterating what you had already posted
Sam, two helpful answers are better than zero! Thank you and Dimitrij for your responses!
:{>
Nice Article
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.
Thanks!!!
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
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.
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
Please can you explain how to perform incremental uploads to multiple tables. All examples are always based on a single source to destination table
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
Thanks for your quick response Andy. Can you do an example of what you have explained? It would be very helpfull indeed.
Hi Tee,
The links in my response are loaded with examples.
Andy
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
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
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
Hi Tee,
Unless the 30 tables are identical in format, you cannot use the same data flow to load them. Are they identical?
Andy
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
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
Hi Andy,
Thank you, Searching for this code. Very useful
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
can u provide an script for c#
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
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