SSIS Expressions setting two variables

Question

I'm trying to use an "Expression Task" within SSIS to simply initialize/set two variables. This seemingly simple task has driven me nuts because I don't know how to separate the statements:

@[User::testVariable]="1"  
@[User::testVar2]=3

That gives a parsing error, and I've read through quite a lot of documentation to no avail. How are statements separated and I hope I don't have to use more than one expression task because I have a lot of variables... Thank you

Garlen

If you need to set a lot of variables, I'd use a script task. – Finnigan

Thanks for the comment: but they are not the same thing. For a Script Task you do not have the option of parameterizing the task, so it's static. I am assigning my variables to parameters and so I need to be able to adjust it at the SSIS level. – Garlen

Not sure what you mean by "parameterizing the task"...do you mean that you can't do what they tell you how to do in this forum answer?: social.msdn.microsoft.com/forums/sqlserver/en-US/… – Finnigan

darn, lol, you COULD set the parameters within Script Task after all! (I'm guessing based on briefly reading that). Would you be kind enough to share a one-liner as to how you'd write a statement that sets VariableA with the value within ParameterA ??? You can add it as an answer if you'd like – Garlen

Well I've never done it myself, but from the link I mentioned it looks like it would be: Dts.Variables("User::VariableA").Value = Dts.Variables("$Project::ParameterA").Value – Finnigan

Answer 1

You can only set 1 variable at a time:

To change the value of a variable during runtime you need an expression or a Script Task. The expression isn't very flexible and the Script Task requires .Net knowledge. That's why Microsoft introduced the Expression Task in SSIS 2012. It enables you to set the value of one variable during runtime. Downside is that it isn't available for 2008 and you can only set one variable at a time.

http://microsoft-ssis.blogspot.com/2013/07/custom-ssis-component-multiple.html

You would have to download a customized Multiple Expressions Task component that is provided on the link or do a Expression Task for each variable.

Kymry

I can't believe it took me so long to pick this Best Answer - even though it helped me and so many others. – Garlen

Answer 2

Something I've started doing recently, which may be helpful to people:

Rather than having to go to the hassle of setting up either multiple Expression Tasks or a Script Task, I've realised it's often possible to use an Execute SQL Task to get the job done. The SQL needed may vary a little based on which database you connect to, my example is based on SQL Server.

My example is based on initialising variables from parameters, but this can be tweaked for different situations.

Step 1

Create a query that accepts parameters in - these are the parameters or variables you need the values from. You could of course use this to initialise a variable to 0 or do some arithmetic on a couple of parameters or variables.

Use the Single row option for the Result Set option.

Step 2

Set up your parameter mapping as usual.

Step 3

Set up your result set.

---

I guess the downside to this is that you're connecting to a database to do it - but if you have SSIS installed, then you have a SQL Server instance on that server - so I'm not sure it's much of a downside.

I find this much less of a hassle than setting up a Script Task, but YMMV.

Pegmatite

it's funny how you've proposed this literally the day after I learned this from a consultant! I attest that this method works, but I am not sure if it's more/less efficient than other methods. Thank you very much for putting up an answer – Garlen