Solved: dtexec /SET package variable value not being used by package

I'm using SQL Server 2012 & MS Visual Studio 2010

I have a DTS package with a user variable, PO (string), and I'm trying to execute a sql command using that variable, so I've set up another variable to build the sql string (SQLStringVar).

The problem is, that when I try to run the package using the DTEXEC utility with /SET, the package is ignoring the variable value I'm sending.

Command I'm using:
 dtexec /server myserver /sql MyPackageName /U user /P password /SET "\Package.Variables[User::PO].Value";"MYPO"

The expression for SQLStringVar is "exec my_procedure '" + @[User::PO] + "'"

However, the results show SQLStringVar as evaluating to:
exec my_procedure ' '
...or whatever value I put for the initial value for the User::PO variable. The package runs without errors.

Thanks!
Dan MerrowAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Dan MerrowConnect With a Mentor Author Commented:
I figured it out:
In the Package Explorer tab...Variables.

Right-click the variable, go to Properties, and change EvaluateAsExpression to False.

(This was driving me nuts since, when editing the variable using the Variables tab, this property is not available)
0
 
Megan BrooksSQL Server ConsultantCommented:
Make sure that the variable is at package scope.

I used to have SSIS generate a configuration file containing the SET information, so that I could just copy it to the command line. I haven't done this in ages, but this blog post seems to describe the process (that I no longer remember): Using DTEXEC and /Set to run SSIS packages

The post seems to be missing its screenshots now, but I think there is a good enough description to be able to follow the steps.
0
 
Megan BrooksSQL Server ConsultantCommented:
OK, I see. Never tried that. :-)
0
 
Dan MerrowAuthor Commented:
Found the answer myself.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.