SSIS Set Variable Custom Task
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.
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.
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).
You can then open the Expression Builder where you can write and test your expressions.
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.
Post By
Josh Twist
6:18 AM
09 Jun 2006
Comments:
Posted by
Deepak Bhagwat
@
23 Jun 2006
12:57 PM
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
6:39 AM
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
8:20 AM
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 AM
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?