CODEWITHSUNDEEP
sqlssis
Daniel Sh.
Daniel Sh.

Reputation: 2074

How do I change the package variable value during runtime?

I'm running an SSIS package and I want to set the value of a desired variable during runtime. Here an example

variable1= 1145554478 nvarchar(30)

I'm using nvarchar since I'm adding more characters to the string.

variable2= [ + variable1 + _S]

variable1 and variable2 are package variables.

I've searched for ways to set a value to the variables during runtime. As I thought, it seems that it could be done with an SQL Task Editor. So, I created one. Set the variable1 as imput value, and ResultSet to single row, and set it to variable2.

then in the code I wrote

set Variable2 = '[' + ? + '_S]'

It's not working, here's the error

Error: 0xC002F210 at Execute SQL Task, Execute SQL Task: Executing the query "set Variable2 = '[' + ? + '_S]'" failed with the following error: "Line 1: Incorrect syntax near '='.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. Task failed: Execute SQL Task.

Is a SQL Task editor the best way of setting a variable during runtime?

I think the code I wrote in the Task editor is wrong, since the code in there is not always the same ordinary SQL used regularly.

Upvotes: 2

Views: 12591

Answers (3)

user756519
user756519

Reputation:

Here is a way to manipulate the value of a variable during runtime based on another variable's value. The sample uses SSIS 2008 R2 but the functionality has been the same on all SSIS versions.

  • Create a new SSIS package.

  • Right-click anywhere on the package and select Variables from the context menu. Variables pane will be displayed.

  • On the Variables pane, create two new variables named Variable1 and Variable2 of data type String.

  • Let's set the Variable1 with the value Test_Value.

    Variables

  • Select the second variable Variable2 and press F4 to bring the Properties pane.

  • On the properties pane, change the property EvaluateAsExpression to True.

  • Then click on the Ellipsis button on the Expression property.

    Properties of Variable2

  • On the Expression Builder dialog, paste the expression "[" + @[User::Variable1] + "_S]" in the Expression text area.

  • If you click on the Evaluate Expression button, you will see the value that the Variable2 would evaluate to. During runtime, the variable Variable2 will automatically evaluate to the value present in Variable1 using the expression.

  • Expression Builder can allow to formulate more complex expressions as well. Click this link to see another expression example.

Hope that helps.

Expression Builder

Upvotes: 3

Matt Smucker
Matt Smucker

Reputation: 5234

You cannot set the value of the SSIS variable in your SQL statement, but rather RETURN the value and assign it to your variable.

Change your statement to

SELECT '[' + ? + '_S]'

Change your result set to "Single Row" and then under "Result Set" on the left add your variable with the Result Name of 0

Upvotes: 3

Eric Petroelje
Eric Petroelje

Reputation: 60498

The easiest way would (typically) be to create a script task and just write the code in C# or VB.

Upvotes: 2

Related Questions