Solved

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

Posted on 2016-11-02
4
20 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 13

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 13

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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Over the last 2 years, I have been working on SSIS 2008. Really the tough tasks in SSIS are to deploy packages and pass parameters (Values from outside package). The latter is certainly a headache for developers, particularly for me. We had to ma…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

744 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

13 Experts available now in Live!

Get 1:1 Help Now