Click here to monitor SSC
  • Av rating:
  • Total votes: 231
  • Total comments: 12
Patrick Index

Passing Variables to and from an SSIS task

20 August 2008

Passing variables to and from an SSIS task makes it useful as an ETL tool, despite all its faults. In the first of a series of articles 'from the trenches', Patrick Index casts a jaundiced eye over SSIS.

Passing Variables to and from an SSIS task

Like it? SSISht!

Like it or loathe it, SSIS is here to stay.  I suppose it’s nice and graphical, and it is also aesthetically  pleasing when you finally get a screen full of green tasks – I tend to leave my screen displaying for a while when this happens, so that everyone can see it whilst I go and make a coffee. SSIS is much richer than DTS.  Additionally you quite often see jobs for SSIS specialists; it would seem that companies are using it as their de-facto ETL tool, standing apart from SQL Server.

SSIS is, by its very nature, frustrating to work with because it is a mish-mash of dissimilar development environments, and I don’t find that the syntax is always intuitive.

There doesn’t seem to be a great deal of material on the web and it can be hard to find good examples to use as guidelines.  So, in the spirit of building up a knowledge base, and hopefully persuading Tony to set up a dedicated section on Simple-Talk for SSIS, I have constructed an example to demonstrate passing variables into and out of an ‘Execute SQL Task and Script Task’.

Passing Variables to and from an ‘Execute SQL Task and Script Task’.

The two tasks do fundamentally the same thing, which is to try and date-stamp a file.  The final variable value “FullPath” could then be easily used by a File System Task to copy/move or delete a file perhaps.

I suppose most SQL Server developers would be more comfortable knocking up this fairly trivial code in SQL, but the difficulty is in passing and catching the input variables in the task.   This example demonstrates the problem.

I have set up a package with three String variables called

  • FileName, which has a data type of String and an initial value of “Import.txt”
  • FolderName, which has a data type of String and an initial value of “c:\”
  • FullPath, which has a data type of String and no initial value

… and an ‘Execute SQL Task and a Script’  Task.

The package is called, rather imaginatively, “Package3”. The scope of the variables is at the package level.  One thing to note when you set up variables (choose SSIS-VARIABLES from the top menu) is to make sure you have clicked on the package and not a task when you create or add a variable.  If you create a variable while being clicked on a task (therefore with task scope) then the variable will disappear from the list when you click up to the package.  Sorry to be an old dog but I initially found this a bit confusing.

The simplest way to inspect your variables is to set a break-point on the task (right click on the task and choose EDIT BREAKPOINTS) for the OnPostExecute event of the task.  This will then allow you to  inspect the value of the variable after the task has completed its execution.  The red dots on the tasks indicate that there are already breakpoints set up on the task.

Doing it the 'Execute SQL Task' way

In the ‘Execute SQL Task Editor’ in the ‘Parameter Mapping’ section, (double-click on the task and choose Parameter mapping), I have set it up so that the two variables i.e. User::FolderName and User::FileName are added to the dialogue box.  Each has a Direction of “Input” which seems logical and a data type of VARCHAR.  The parameter names that I have used are just the ordinal positions of 0 and 1, which is what the context help suggests.  In other words, the value of User::FolderName is the first input parameter and User::FileName is the second input parameter.  The parameter lengths are 50. In other words, we are dealing with a varchar(50) parameter.  The initial default values for these, when you set them up, are -1 which tells me nothing I am afraid.

For the Result Set options on the Execute SQL Task, I have put in the aliased name of the output column from my query, which, giving my poetic instincts full rein, I have called FullPathFromQuery, and I want to pass this to my variable User::FullPath.

‘So what about the query?’ you may ask.  Well, if we go back to the General option on the Execute SQL Task Editor, you can see the code and I will list it again here

DECLARE @YesterdaysDate varchar(8)

DECLARE @Filename varchar(50)

DECLARE @Folder varchar(50)

DECLARE @Etc varchar(50)

SET @Folder = ?

SET @Filename = ?

--SET @Etc = ?

SELECT @YesterdaysDate = CONVERT(varchar(8),DATEADD(dd,-1,getdate()),112)

SELECT @Folder + SUBSTRING(@Filename,1,CHARINDEX('.txt',@Filename)-1) + '_' + @YesterdaysDate + '.txt' AS FullPathFromQuery

For such trivial code you would not want to set up a stored procedure I suspect, so the option of passing parameters to a stored procedure is not really there.

The only way to pick up these input variable values is to use question marks “?” in the order that they are passed.  This query as it stands will clearly not parse in query analyser window so you can only really test your code by running it in the task: This is not ideal.

You must also set the ResultSet option to be “Single row”.

If you run this to the break point one can see that the variable User::FullPath has been assigned the value  C:\\Import_200870805.txt ( I ran this on 6th Aug  2008) so the code must be working.

Using a Script Task instead

Perhaps a simpler way to do this is just to use the Script Task.  The trivial code again demonstrates how to pick up and assign values to and from variables in the vb.net code.  You need to tell the task which variables it is going to use by adding them to the ReadOnlyVariables  and ReadWriteVariables options in the Script Task editor window and also be aware that the variables names are case-sensitive.

The code which shows the passing and assignment of the variables in the script is shown below.

Imports System

Imports System.Data

Imports System.Math

Imports Microsoft.SqlServer.Dts.Runtime

 

Public Class ScriptMain

 

      Public Sub Main()

           

 

        Dim strFolder As String

        Dim strFilename As String

        Dim strTomorrow As String

        Dim strNewFullPath As String

 

        'do path in script

        strFolder = Dts.Variables("FolderName").Value.ToString

        strFilename = Dts.Variables("FileName").Value.ToString()

        strTomorrow = CStr(Format(Today().AddDays(+1), "yyyyMMdd"))

  strNewFullPath = strFolder & strFilename.Substring(0,    strFilename.IndexOf(".txt")) & "_" & strTomorrow & ".txt"

 

        'display new value of path

        'MsgBox(strNewFullPath)

 

        Dts.Variables("FullPath").Value = strNewFullPath

        Dts.TaskResult = Dts.Results.Success

 

      End Sub

 

End Class

 

If you put a breakpoint on the task the value of the variable can be inspected to give tomorrows date, and it should look like this…

So which approach is best? 

People tell me that using question marks was how it was in DTS, but I have to say I didn’t find setting up the Execute SQL Task particularly intuitive.  The script task for me seems like the simplest solution of the two, surprisingly.

So the example is not going to win a Nobel Peace Prize for complexity but it does demonstrate the awkwardness of SSIS in the real world.  Hopefully the article will encourage readers to publish their experiences using the various tasks and we can build up a knowledge base of real world examples for everyone to reference on the simple-talk site.  I have recently been wrestling with the XML task and data source which will be the subject of my next article.

Patrick Index

Author profile:

I have been contracting for 14 years and before that I worked for a software house for 5 years in the late 80's. I started out as a Lotus 1-2-3 developer and then did database development with Paradox. That soon became Access development which progressed to Access with SQL Server in a client server scenario and now I suppose I would describe myself as a SQL Server developer.

Search for other articles by Patrick Index

Rate this article:   Avg rating: from a total of 231 votes.


Poor

OK

Good

Great

Must read
Have Your Say
Do you have an opinion on this article? Then add your comment below:
You must be logged in to post to this forum

Click here to log in.


Subject: Passing values between a sub package and a parent package
Posted by: NikA (view profile)
Posted on: Wednesday, September 03, 2008 at 7:06 PM
Message: Hi Patrick

Thanks for writing this article. Our company has recently migrated our Data Warehouse from SQL2000 to SQL2005 and passing values from tasks to variables in SQL2005 was something we needed to work out. So this article has been very useful for us.

To take this a step further, is it possible to pass values between a sub package and a parent package? If so how is this done? The reason I ask is that most of our load jobs have a main control / master package that calls sub packages and ideally we would like to pass execution info etc in both directions.

Regards
Nik


Subject: Re: Passing values between a sub package and a parent package
Posted by: Patrick Index (view profile)
Posted on: Thursday, September 04, 2008 at 8:28 AM
Message: Nik

I believe that a sub-package can read the variables of its parent but you can't go the other way. One workaround would be to use package configurations (using SQL tables) and pass values back and forth here.

Paddy

Subject: Re:Passing values between a sub package and a parent package
Posted by: Patrick Index (view profile)
Posted on: Thursday, September 04, 2008 at 8:35 AM
Message: Nik

You also have to set the DelayValidation property to True to disable the design time validation on the Execute package task (Parent->sub-package only).

Paddy

Subject: Re:Passing values between a sub package and a parent package
Posted by: jack78 (view profile)
Posted on: Thursday, September 04, 2008 at 10:23 PM
Message: Hi,

I'm new to SSIS.... I’m trying to use the variable value (a complete filename) created in the script task to load the file into a Data Flow task, but the variable is not passing the value through so that it can be used in the Data Flow task. Any suggestions are welcomed!

Subject: Query from Jack78
Posted by: Patrick Index (view profile)
Posted on: Friday, September 05, 2008 at 4:03 AM
Message: Couple of things.
1.Beware case sensitivity
2.In the script task the variable should be listed in ReadWriteVariables option

3. The connection manager for the DataFlow task may need an expression defined for the ConnectionStringProperty along the lines of

@[User::ImportFolder] + "\\" + @[User::FileBeingImported]

depending on how you have set up your variables. More bizarre syntax I am afraid!

Hope this helps.

Paddy

Subject: Query from Jack78
Posted by: jack78 (view profile)
Posted on: Tuesday, September 09, 2008 at 1:07 AM
Message: Hi Paddy,

Just wanted to say thank you for the info, it was of great help. I did eventually get it working (carefully following instructions on this post) 

Cheers,

Jacky

Subject: Hey Patrick--
Posted by: gmartinnc (view profile)
Posted on: Friday, March 20, 2009 at 9:12 AM
Message: Variable passing: this is great! Thanks for such a clear story.

You probably already know this, but to give everyone out there a little info, it turns out only ADO.NET connections support named parameters. I was using OLEDB and should have used numbered parameters, as you described, and couldn't get it to work. There is a reference table available at http://sqljunkies.com/WebLog/knight_reign/archive/2005/10/05/17016.aspx. Not much outside of ADO.NET.

Graeme
PS: Great last name for a SSIS Developer! I'm sure you NEVER heard that before... :-O

Subject: Variable Passing using ssis
Posted by: srinivasa (view profile)
Posted on: Friday, June 26, 2009 at 7:17 AM
Message: Thanks a ton Pat, the example that you provided is very helpful.

Thanks,
srinivasa

Subject: Pass File Creation Date
Posted by: mrdata (view profile)
Posted on: Tuesday, August 11, 2009 at 2:02 PM
Message: Hi Pat. This is great.
How can I retrieve the file.creationdate or the file.lastmodifiedtime? Is there an expression available or do i have to do a script code? Do you have an example?
Thanks
Sam

Subject: Great Article
Posted by: Vicky (view profile)
Posted on: Wednesday, June 30, 2010 at 2:13 PM
Message: Thanks so much for this post, helped me out a lot!

Subject: nice article
Posted by: budi bong (view profile)
Posted on: Wednesday, November 02, 2011 at 8:12 AM
Message: hi,nice article n useful for newbie like me. thanks.

Subject: Thanks!
Posted by: kilokiloj (view profile)
Posted on: Tuesday, March 26, 2013 at 4:56 PM
Message: I never do this because most of these are a celebration of ignorance. I was able to follow and implement the solution without problem. I'm sure it saved be hours.

Thanks -

 

Phil Factor
Searching for Strings in SQL Server Databases

Sometimes, you just want to do a search in a SQL Server database as if you were using a search engine like Google.... Read more...

 View the blog

Top Rated

Continuous Delivery and the Database
 Continuous Delivery is fairly generally understood to be an effective way of tackling the problems of... Read more...

The SQL Server Sqlio Utility
 If, before deployment, you need to push the limits of your disk subsystem in order to determine whether... Read more...

The PoSh DBA - Reading and Filtering Errors
 DBAs regularly need to keep an eye on the error logs of all their SQL Servers, and the event logs of... Read more...

MySQL Compare: The Manual That Time Forgot, Part 1
 Although SQL Compare, for SQL Server, is one of Red Gate's best-known products, there are also 'sister'... Read more...

Highway to Database Recovery
 Discover the best backup and recovery articles on Simple-Talk, all in one place. Read more...

Most Viewed

Beginning SQL Server 2005 Reporting Services Part 1
 Steve Joubert begins an in-depth tour of SQL Server 2005 Reporting Services with a step-by-step guide... Read more...

Ten Common Database Design Mistakes
 If database design is done right, then the development, deployment and subsequent performance in... Read more...

SQL Server Index Basics
 Given the fundamental importance of indexes in databases, it always comes as a surprise how often the... Read more...

Reading and Writing Files in SQL Server using T-SQL
 SQL Server provides several "standard" techniques by which to read and write to files but, just... Read more...

Concatenating Row Values in Transact-SQL
 It is an interesting problem in Transact SQL, for which there are a number of solutions and... Read more...

Why Join

Over 400,000 Microsoft professionals subscribe to the Simple-Talk technical journal. Join today, it's fast, simple, free and secure.