2

I'm having a problem transitioning a 2005 package to 2008 - it appears that in 2008, package variables configured to use a Configuration Filter (eg populate from [SSIS Configurations]) will not honor the /SET command provided by dtexec.exe to override a package variable value at runtime.

The issue is documented here http://dougbert.com/blogs/dougbert/archive/2009/04/07/understand-how-ssis-package-configurations-are-applied.aspx

What's the most straight-forward solution for this? I want the old SSIS 2005 behavior where, a package variable is initially loaded from [SSIS Configurations] but I can override it at runtime if I explicitly call /SET

I have a work-around but am hoping for a better solution - I basically have 2 versions of a variable I want ...eg NETWORK_PATH, NETWORK_PATH_CONFIG ...I put an expression on NETWORK_PATH to use the NETWORK_PATH_CONFIG (this variable would be populated from [SSIS Configurations]) if the value of NETWORK_PATH is initially NULL at runtime when the expression is first evaluated otherwise use the value that was provided (presumably by dtexec /SET)

manning18
  • 957
  • 3
  • 8
  • 18

1 Answers1

0

You either use /Conn to change the location of your config settings and have the different settings there or you remove the variable from the configurations completely so that it can be set in the dtexec /set parameter.

If you want to have a default and override then you will have to go with the /conn parameter and change the location of the settings.

e.g.

"C:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn\DTExec.exe"
/File "C:\Path\To\My\Package\Import Types.dtsx" /Conn Connection1;"Provider=SQLNCLI10;Server=MYSERVER;Database=DB_ONE;Uid=USERNAME;Pwd=PASSWORD;" /Conn Connection2;"Provider=SQLNCLI10;Server=MYSERVER;Database=DB_TWO;Uid=USERNAME;Pwd=PASSWORD;"

It appears someone thought this was better than the 2005 method. I would like to know his rationale before I hit him.

price
  • 1
  • Ouch - that's definitely a solution I had not considered, but having to maintain separate DB instances, simply to contain separate sets of values isn't an ideal solution but thanks I wasn't aware of this technique. Ultimately I ended up going with my original approach of having a separate "override" version of each variable I want configured, with the original variable having an Expression of the form: ISNULL(@[User::MAIL_RECIPIENTS_OVERRIDE]) ? @[User::MAIL_RECIPIENTS] : @[User::MAIL_RECIPIENTS_OVERRIDE] – manning18 Sep 27 '11 at 19:00