SSIS Set Variable Custom Task

IMPORTANT This post and the corresponding code was created some years ago. I've hardly worked with SSIS since and so don't plan to update the code to support the latest versions of SQL Server. The component and source were designed for SQL Server 2005 and will not work in SQL Server 2008 or later. You can take the code and change it at your will (I hear it's a simple fix). I considered pulling the posts altogether but a number of people have found value in these ideas even if they have to modify the code.

One of the most common usages of the good old script component in SSIS is to modify variables, ready for use by other tasks. This works a treat, because manipulating variables in script comes quite naturally to us.

However, I often find this process quite onerous. I have to type out the variable names multiple times (once to declare which variables I will be reading and writing and once again in the script itself). Furthermore, there isn't any checking at design-time of the variable names so typos go unnoticed until runtime.

It struck me that a custom task with two properties, VariableName and VariableValue, could solve most of these issues and so the Set Variable Task was born.

The full source is available or you can just download the msi if you prefer.

It's pretty easy to use and even comes with its own UI which allows you to choose from the available variables and set a static value. Note that readonly variables aren't listed, for obvious reasons.

The Set Variable Task's UI

I can imagine a few scenarios where you might want to change a variable in a pre-defined way as a control flow proceeds (maybe to store state of some kind), but I think this task becomes really useful when you use it in conjunction with Expressions.

Expressions allow you to set the value of certain properties in a dynamic way at run-time. To access expressions, simply select the task and then look for the 'Expressions' entry in the properties window.

Expressions in the Properties window

Clicking the ellipsis button (...) will bring up the Property Expressions Editor where you just have to choose the property you want to play with (which is VariableValue in this case).

The Property Expressions Editor

You can then open the Expression Builder where you can write and test your expressions.

The Expression Builder

In the screenshot above I'd written a little expression to generate a DateTime stamp to be used in a file in the format YYYYMMDD, so 20060609 which is pretty handy - the actual expression looks like this.

(DT_STR, 4, 1252) YEAR( GETDATE()) +
RIGHT("0" + (DT_STR, 2, 1252) MONTH( GETDATE()), 2) +
RIGHT("0" + (DT_STR, 2, 1252) DAY( GETDATE()), 2)

The only weird thing is, because the VariableValue property is of type string, you have to pass it a string - even if your variable is actually an int. But don't worry - the code tries its best to convert the string to the appropriate type for storage in the variable.

Another use case for this task could be to increment a variable, with the following expression (for a variable called User::MyCount).

(DT_STR,10,1252) (@[User::MyCount] + 1) + ""

(Not sure why I had to put the additional + "" on the end, but it works)

I guess the real advantage of this task over the script component is less typing and much more runtime validation but as usual, your own mileage may vary. Maybe it's of use to somebody out there - if you have any thoughts or requests be sure to leave a comment.

UPDATE - 16 June 2006 - I've just updated the source and msi to improve the validation. You might want to get a fresh download.

Tags: SQL

Post By Josh Twist

Follow @joshtwist

06:18
09 Jun 2006

» Next Post: Assembly Initialize and CleanUp in Test Projects
« Previous Post: Resolve function in Visual Studio 2005

Comments are closed for this post.

Posted by Deepak Bhagwat @ 23 Jun 2006 12:57
Josh,

I will briefly explain the task I have to do and then I will ask you a specific question. Ok, so here it goes -

I am trying to come up with a package where a ODS database is refreshed everyday. I copy a backup from a source location and place it at a dest location from where I use this file to restore a database. On this database I run some scripts to make it an ODS. So far so good...Now within this package, I need to set up a variable, such that if the value is 1 then certain tasks will be executed and if 0 then certain tasks won't be executed. Here is the workflow....

COpy FIle -> Take Active ODS Backup -> Restore ODS From New Backup -> Run DDL -> Run DML -> Resync the DB users

If any step fails from Restore ODS From New Backup to the last step, then it should just move to the failover path by restoring the working copy of the ODS from the active ODS backup so on and so forth...

Is there a way to do it using your set variable task.

I am a newbie...hence not getting the idea...

Help me out here...

Thanks,
Deepak.

Posted by Tim @ 22 May 2007 06:39
Could this Task be used to set variables by Looping through a DataReader or ResultSet Variable? Let's say I have returned a ResultSet, now for each row I want to set a Variable to that row's ID based on a condition in that Row.

Example:
ResultSet
ID Name
1 Test 1
2 Test 2

Variable: User::Var1
Condition: Row[Name] = "Test 1"
Value: ID

Variable: User::Var2
Condition: Row[Name] = "Test 2"
Value: ID

Posted by Ricardo Davis @ 30 May 2008 08:20
Josh,

This is a great add-in! May Microsoft pay you license fees to use it in an upcoming SQL Server release.

One request: Could you add the random number function (RAND()) to the list of math functions available?

-RD

Posted by Mick Haney @ 27 Aug 2008 11:49
I like this.. But I wanted to use it on the data flow tab to set the file name.. It only shows up on the control flow when I install it.. I am new to SSIS.. In my scenario, I want several source to target mappings in a single data flow using a single connection manager.. I want to vary the file name for each target (EG outfile1, outfile2, etc....) I was hoping to create a global variable for the file name, and in the data flow, add this task prior to the query then populate tasks.. Any ideas?

Posted by Michael Webb @ 30 Oct 2008 08:59
Hey I am trying to use this task. I get an error that says I need Register the task. Any help?

Posted by Nicolas @ 05 Nov 2008 06:14
I'm currently upgrading 2005 ssis packages using vs2008. I can't get this component working properly on vs 2008. I tried to compile the source (I updated some references) using vs2008, then copying the generated dll file to the sql10 dts task folder. But I didn't manage to get it working. So if someone could help me regarding this issue, thanks in advance.

Posted by Mark Ogilvie @ 17 Nov 2008 20:08
I'm having problems running a package with a SetVariable task on a 64 bit server, when running the package via DTEXEC. It throws an error "You must specify a variable name" during task validation.

The package runs fine from the Visual Studio GUI.

Any suggestions?

Posted by Log @ 05 Dec 2008 03:06
I'm using a ssis 2005 package which has your setVariableTask. Now, i'm process of migrating the package to ssis 2008. Now, the migration is getting failed coz of the setVariableTask. Can you provide a soln to me on this regard.
Thanks,
Log

Posted by Todd McDermid @ 19 Jan 2009 13:33
I've included your task in a directory of custom tasks and components for SSIS on CodePlex. Hopefully more people can benefit from the effort you've put in. Thanks for contributing!

Please visit the SSIS Community Tasks and Components project at http://www.codeplex.com/ssisctc.

Posted by Don @ 10 Feb 2009 20:52
The SetVariableTask is giving me a "Error: The wrapper was unable to set the value of the variable specified in the ExecutionValueVariable property." error. The variable I want to set is of type string. I select that variable in the Variable dropdown. I use the Expression editor to set the value of Value ('(DT_STR, 4, 1252) "[" + @[System::UserName] + "].[invalidTPaccounts]"'). The Evaluate Exprression button/function produces the desired result. That proper value shows up in the Value textbox. What am I forgetting? Thanks

Posted by SP @ 19 Mar 2009 02:56
Hello there
I am having some issues in getting this task to work on windows 2003 server, I get an error message saying "is not registered for use in this computer" any pointers will be much appreciated.

Regards
Sridhar

Posted by Michael Reyeros @ 22 Jun 2009 10:12
I am having the same problem as Sridhar, "is not registered for use in this computer". Any help would be appreciated.

Thanks
Michael

Posted by Paul Wallington @ 08 Jul 2009 07:44
Hello guys,

Any chance of a SSIS 2008 version of this task ? This version only appears to work on SSIS 2005

Thanks
Paul

Posted by josh @ 09 Jul 2009 00:51
Sorry Paul, I have no plans to update this as I haven't worked with SSIS for over 3 years.

I'm more than happy for you to take and update the code and re-publish though.

Posted by Joe D. Sullivan III @ 04 Aug 2009 12:22
I was working on modifying the source code to work with 2008 but did not finish. Anybody do this yet?

Joe

Posted by Gary Pope @ 24 Nov 2009 07:42
I have a package with several set variable tasks which runs fine from visual studio, however when I install it on the sever to run from MSDB it fails validation.
Error: You must specify a variable name.

Any idea how I get this to run on an ssis server?

Thanks Gary

Posted by Sanja @ 02 Jul 2010 12:09
Hi,
Any chance I can find a version for SSIS 2008? Perhaps someone had fun with the source code upgrade?
many thanks,
Sanja

Posted by Faiz @ 16 Jul 2010 07:02
How about an easy work around, check http://faiz.kera.la/2010/07/16/set-ssis-variable-without-using-script-component/

Posted by Sam Aaron @ 09 Sep 2010 18:34
@SP & @Michael Reyeros ; I had the same problem with the registeration and these are the steps i followed to fix the issue:

1. Open SQL Server configuration mangager
2. Choose SQL server 2005 services
3. Right click SQL Server integration services, choose properties
4. Choose log on tab, select this account, enter your username and password which is used to log on your computer.
5. Reopen the business intelligence studio. There you are, the data flow task component can work properly again!!!
source: http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/ef6ac593-ae9d-4875-9060-db183b6fd4ed?prof=required

Welcome

Feeds

Archive

Editors