2

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.

Daniel Sh.
  • 2,078
  • 5
  • 42
  • 67
  • Yes Siva, you are not missing anything. Ive solve the issue from a Task Editor, but Id love to know your opinion too. Thanks – Daniel Sh. Apr 13 '12 at 15:09

3 Answers3

3

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

msmucker0527
  • 5,164
  • 2
  • 22
  • 36
  • Nice one msmucker, thanks a lot, it worked. Didn't know about the '0' in the ResultSet – Daniel Sh. Apr 13 '12 at 14:40
  • 1
    It's the index of the column for the single row result set, so if you were to select something else (SELECT '[' + ? + '_S]', 'SomethingElse') you would use the Result Name of 1 – msmucker0527 Apr 13 '12 at 14:44
  • And so on, I get it now. Thanks a lot! this will be usefull in future processes. – Daniel Sh. Apr 13 '12 at 14:46
3

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

Community
  • 1
  • 1
  • Thanks so much Siva. Your explanation was not just correct but so very well explained. Thanks again, a lot. – Daniel Sh. Apr 13 '12 at 15:40
2

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

Eric Petroelje
  • 59,820
  • 9
  • 127
  • 177
  • Im not versed enough in C# to create even a simple code like that. that's why im trying to do it from SQL. Will google some C# though – Daniel Sh. Apr 13 '12 at 14:32
  • @Daniel - google should help you here. The code you'd need to write should only be one line or so. – Eric Petroelje Apr 13 '12 at 14:36