variable set in design time but not run time

Question

variable set in design time but not run time

cafescott

SSCarpal Tunnel

Points: 4724
More actions
November 14, 2012 at 7:48 am

#284942

Hello,
I have a package with two global variables. One is an ID to identify a customer; e.g., 215. The other variable is a path on our network that references the customer_ID. The expression for the latter resembles:
"\\\\ourserver\\our volume\\Lots_of_Directories\\Package_folder\\Dir_" + (DT_STR, 9, 1252) @[User::Customer_ID]
When I evaluate the expression of the variable (in design time) it looks fine; e.g.,
\\ourserver\our volume\Lots_of_Directories\Package_folder\Dir_215
However, when I debug the package, I have a script task at the top which spits out the value of the folder in a message box. This value only shows the default value that is defined in the variables window.
I understand that I can set up a script task to set the value of the folder variable that uses the customer_id variable. However, I am wondering why I have to do this, and why SSIS isn't automatically setting the same value at runtime that appears in the expression builder screen.
Does anyone know why? thanks.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

How is the value of the Customer_ID variable assigned? If it isn't set by any logic within the package then it will use at runtime the value of the variable when the package was loaded. Try setting it explicitly in the package or using Package Configurations to get the value from a table or config file. If you want it to work as a one-off, try using the Reload with Upgrade option from Solution Explorer.

John

cafescott SSCarpal Tunnel Points: 4724 More actions · Answer 2

Thanks for the reply. I disabled all but two script tasks in the package. The top-most script task sets the value of the customer_id. The second script task uses a MsgBox to display the value of the variable that is defined in the expressions window as the concatenation of a network path and the customer_id. In the variables window I have also emptied the default value of the variable (with network path and customer_id).

When I look at the variable in the expression window (in design time), it looks fine (i.e., network path and customer id appear). However, when I run the package, the variable is empty, not being set by the expression.

I also ran "upgrade all packages" and it successfully ran. However, I'm getting the same result.

This is pretty strange. I haven't seen seen this behavior yet.

It appears that I'm going to have to just set the value of the variable in a script task because the design-time expression is being ignored, for reasons unknown. Thanks.

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 3

Don't use a script task. It's more complicated and it means you have to edit the package every time you want to change the value of the variable. Get the value of the variable from a config file or a table using Package Configurations. If you're not already using Package Configurations, you should take some time to learn how it works, since it makes things a lot easier when you make changes such as moving the package to run on a differnet server.

John

cafescott SSCarpal Tunnel Points: 4724 More actions · Answer 4

Thanks, John Mitchell-245523. I use package configurations for a lot of purposes. However, in this utility package, it appears to be preferable to set the customer id in the variables window.

thanks,