Solved

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

Posted on 2016-11-02
4
27 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
  • 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 14

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 14

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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Lessons learned during ten years of interviewing for SQL Server Integration Services (SSIS) and other Extract-Transform-Load (ETL) contract roles and two years of staff manager interviewing contractors.
My client sends a request to me that they want me to load data, which will be returned by Web Service APIs, and do some transformation before importing to database. In this article, I will provide an approach to load data with Web Service Task and X…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
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.

863 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

Need Help in Real-Time?

Connect with top rated Experts

26 Experts available now in Live!

Get 1:1 Help Now