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...
-Jamie