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

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

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

Comments

[LearnByReading]

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

[LearnByReading]

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