Solved

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

Posted on 2016-11-02
4
51 Views
Last Modified: 2016-11-22
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!
0
Comment
Question by:Dan Merrow
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 

Accepted Solution

by:
Dan Merrow earned 0 total points
ID: 41871308
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
 
LVL 15

Expert Comment

by:Megan Brooks
ID: 41871330
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
 
LVL 15

Expert Comment

by:Megan Brooks
ID: 41871332
OK, I see. Never tried that. :-)
0
 

Author Closing Comment

by:Dan Merrow
ID: 41897223
Found the answer myself.
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
A couple of weeks ago, my client requested me to implement a SSIS package that allows them to download their files from a FTP server and archives them. Microsoft SSIS is the powerful tool which allows us to proceed multiple files at same time even w…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question