Solved

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

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

Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

Question has a verified solution.

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

In couple weeks ago, I encountered an extremely difficult problem while deploying 2008 SSIS packages to a new environment (SQL Server 2014 standard).  My scenario is: We have one C# application that is calling 2008R2 SSIS packages to load text fi…
My client has a dictionary table. They're defining a list of standard naming convention. Now, they are requiring my team to provide us a mechanism how to match new incoming data with existing data in their system.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

696 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