Welcome to EMC Consulting Blogs Sign in | Join | Help

SSIS Junkie

SSIS: Initialise variables the easy way

Its possible that at some point when building SSIS packages that you will want to initialise a variable with some value at execution time and the most obvious way of doing this is to use a script task. That code would look something like the following:

Public Sub Main() Dim vars As Variables Dts.VariableDispenser.LockForWrite("User::Variable") Dts.VariableDispenser.GetVariables(vars) vars("User::Variable").Value = "Some silly string" vars.Unlock() Dts.TaskResult = ScriptResults.Success End Sub

Not everyone likes writing code mind you so here's a nifty little trick you could try instead. The ForEach Loop uses enumerators to define the collection that needs looping over; one of those enumerators is the ForEach Item enumerator which simply allows you to type in values at design-time that will be looped over at execution time. Very simple stuff indeed. Here you can see that I've set up a ForEach Loop accordingly:


I've defined a 1-column, 1-row collection containing a single value "Some Silly String". On the Variable Mappings tab I just have to assign that value into a variable like so:


Errr and that's it, the variable User::Variable is initialised with the value "Some Silly String". Admittedly I can't really think of a practical use for this (can you think of one?) but at least it introduces the ForEach Item Enumerator, a little known feature of SSIS. Truthfully I've only written this to kill some time on a long car journey but hopefully it proves useful to someone out there, let me know if so. Until next time...


Published Tuesday, September 09, 2008 7:58 PM by jamie.thomson



Eric Wisdahl said:

Very silly indeed.  :-)

September 10, 2008 1:29 PM

Gareth said:


We did come up with a practical use.  Don't want to this to sound like a product plug (so I won't mention the name of it), but we pass over an XML file which contains information about all the exports from our app in our SSIS custom control task, this could be file references for a bunch of csv files, OLEDB connection strings where data was loaded updated etc, which you can then iterate through using the XML NodeList enumerator with XPath and then act on each discrete export accordingly.

For example you might throw the filename into a flat file data source (using the  variable for the filename you grabbed earlier for the connection string) and set each one off on a further subtask and eventual load.

I know the ForEach File enumerator can do this, but only if they are in the same folder/subfolder structure.

Reading out from an XML file also allows the passing of some more interesting information into variables for conditional processing on each file/previous export.


September 10, 2008 3:11 PM

Paul Smith said:

You can also achieve this with a SQL Task, given that you have the nessesary connection in your package

September 11, 2008 10:45 AM

Jarrett said:

Not sure I understand the point of this post but if you want to "initialize a variable with some value at execution time" just expose the variable in your configuration.

September 18, 2008 4:51 PM

jamie.thomson said:

Correct jarrett. But there might sometimes be a need to do this during package development. Certainly there is no need for it in a production environment.

September 18, 2008 5:14 PM
New Comments to this post are disabled

This Blog


Powered by Community Server (Personal Edition), by Telligent Systems