CODEWITHSUNDEEP
sql-server-2008ssisexpression
V.B
V.B

Reputation: 1211

SSIS Variable as an Expression for another variable

I am working with SSIS 2008. I have a variable which takes its value from configuration file. I want to assign its value to another variable. For this I created another variable and set its EvaluateAsExpression property to TRUE and wrote the variable (which is taking value from configuration file) as its expression. But at run time the assignment is not happening.Even though there is no error.

EDIT The reason I am doing variable assignment is if the end user does't give any values to the variables in configuration file, I want to assign some default values to those variables and then assign the variables as expression to other variables. All this is I am trying to do in a Script Task. don't know if this the only way to do such things.

Upvotes: 0

Views: 10127

Answers (1)

Bert Wagner
Bert Wagner

Reputation: 881

I was able to get this to work without any issue; I couldn't replicate your scenario. Here's what I did:

  1. Set up two package level string variables: "Var1" and "Var2".
  2. I opened up my Package Configurations (SSIS>Package Configurations) and created a new mapping that sets the Value property of my Var1 variable from a config file. (See the MSDN article about Package Configurations for more information: http://msdn.microsoft.com/en-us/library/cc895212.aspx)

  3. In the Variables window (View>Other Windows>Variables) I set the expression for my Var2 equal to

    @[User::Var1]

The above steps correctly gave Var2 the value for Var1 that was getting set from an outside configuration file. To test that this worked, I added a Script Task and

  1. In the Script Task Editor, added User::Var2 as a Read Only variable

  2. In the body of my script, I added the following code to see the value of Var2 at runtime:

    public void Main() { MessageBox.Show(Dts.Variables[0].Value.ToString()); }

At runtime, the message box that pops up shows the correct value.

I did notice however that the Value property of Var2 in the Variables window does not always show the correct value - sometimes it shows whatever was set to the default value of Var1. Even with this discrepancy in the Variables window, when using Var2 in my actual package code it always got set to the correct value.

Upvotes: 1

Related Questions