June 16, 2016 at 1:03 am
In a simple SSIS 2012 package I'm trying to change the value of a variable (name vValue).
The default value is based on a project parameter: $Project::OldValue
The ReadOnly property is set to false.
EvaluateAsExpression is set to true with expression set to @[$Project::OldValue]
I'm using a Expression Task to change value of the "vValue" variable with the following expression:
@[User::vValue] = @[$Project::NewValue]
However breakpoints on the task show that the variables value hasn't changed.
Tried the same with a script task, to no avail.
Is this a known issue or am I doing something wrong?
June 16, 2016 at 1:47 am
There is nothing wrong SSIS doing, Following is the understanding.
If you are Setting a variable with expression. then if you want to change the value of that variable you need to change the expression not the value.
hope it helps.
June 16, 2016 at 3:26 am
Don't think I follow.
How would you change the expression at runtime?
Surely you should be able to change the value of a variable. Hence the name.
June 16, 2016 at 4:53 am
xanthos (6/16/2016)
Don't think I follow.
Let me rephrase, If you have used Expression on any variable, if you try to change the value of that variable even on design time, the Expression will override the value u just set. This is how expression works.
for more understanding regarding expression see Link
How would you change the expression at runtime?
Yes you can using Script Task, by exploring the Expression property of the variable. like below:
Dts.Variables["User::vValue"].Expression
Surely you should be able to change the value of a variable. Hence the name.
I would say if you want to use a variable and want set different values to it throughout the package execution then i would recommend instead of using the expression use Expression Task.
Hope it helps.
June 16, 2016 at 5:28 am
Thank you for your help. Really appreciated.
Expression Task would be my prefered method. Attached a package that shows what I'm trying To do.
I can get it to work if I don't set a default for the variable. But in my real package I need a default as I'm using the variable in a connection.
June 16, 2016 at 5:51 am
Your problem is that you need to have a default value for your variable. Right?
If the answer is Yes then you can achieve it very easily. doing the following:
1. open your current package.
2. remove the expression from the variable.
3. Drag Drop a Expression Task on your package and name it "Set Default Task".
4. Make sure "Set Default Task" should be your 1st task to be executed in your package.
5. Set the value of your variable with your default value.
6. Save the package and run it.
I have attached a sample package for u.
hope it helps.
June 16, 2016 at 6:24 am
Thanks.
I need to have the default set as soon as I open the package otherwise my data flows that use the connection come up with warnings.
The variable is used in an expression for the initial catalog.
I've managed to work around this by adding a condition to the initial catalog expression.
In my example I have to connect to a database snapshot if the package created one. When a snapshot has been created, a value of a variable will change.
This changed variable is used in the expression of the initial catalog property of the connection. The connection will now use the database snapshot instead of the original database.
The creation of snapshot is an option set by a project parameter.
Thanks again for your help.
Am I right in concluding that if you default a variable to an expression, the variable becomes a constant?
June 16, 2016 at 6:50 am
Attached package with 2 variables:vValue depends on the value of vShowNew
If vShowNew is changed by the Expression Task, the value for vValue will change to the value of the NewValue parameter.
June 17, 2016 at 12:10 am
xanthos (6/16/2016)
Thanks.I need to have the default set as soon as I open the package otherwise my data flows that use the connection come up with warnings.
The variable is used in an expression for the initial catalog.
That's not an issue, Its is Design Time whenever you open a package BIDS check the text Connectivity as by default WorkOffline mode is off. Have developed many packages and 99% of the time my connection string is based upon the variables never needed a default value. Because variable value set at run time. Do remember, your connection won't go for a connection until its been used by any SQL Script Task/ Dataflow task.
So If your Package Flow is
1. Set the connection string.
2. perform Dataflow/SQL script Task.
then your package will wont fail until you have not set the connection string properly or your server is down.
I've managed to work around this by adding a condition to the initial catalog expression.
In my example I have to connect to a database snapshot if the package created one. When a snapshot has been created, a value of a variable will change.
This changed variable is used in the expression of the initial catalog property of the connection. The connection will now use the database snapshot instead of the original database.
The creation of snapshot is an option set by a project parameter.
This is my understanding so far, You have single Connection, and you want to Change initial catalog depending upon value of Flag/Indicator. then your approach is fine. As you mentioned the other db is a snapshot the original db, i assume your schema will be same on both side then this will also not hurt your Dataflow task either.
Am I right in concluding that if you default a variable to an expression, the variable becomes a constant?
As i mentioned in my earlier replies, Not it will not be a constant that you have set an expression on the variable and you can set the variable expression. So i would say NO what you have concluded so far.
June 17, 2016 at 12:32 am
I have seen your logic changing the value based upon a flag that is fine nothing wrong in it.
I have also attached a sample package to show you can execute the package without setting the default value on a connection string.
hope it helps.
December 10, 2024 at 7:36 pm
Good day,
Can I get this package as well?
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply