August 23, 2006 at 9:37 am
I am using a stored proc to call DTXEC thru xp_cmdshell and having problems dynamically setting the connectionstring using the \SET command option. Am trying to execute the same package but dynamically set the server. Here is the excerpt of the DTEXEC which is giving me problems.
execute
master..xp_cmdshell 'DTEXEC /set "\Package.Connections[myconnectionid].Properties[ConnectionString];Data Source=MyServer;User ID=userid;Password=password;Initial Catalog=DatabaseName;Provider=SQLNCLI.1;Persist Security Info=True;Auto Translate=False"'
Am getting the following error:
Argument ""\Package.Connections[ntsdb.epacube].Properties[ConnectionString];Data Source=MyServer;User ID=userid;Password=password;Initial Catalog=DatabaseName;Provider=SQLNCLI.1;Persist Security Info=True;Auto Translate=False"" for option "set" is not valid.
Anyone have any idea what I am doing wrong?
August 28, 2006 at 8:00 am
This was removed by the editor as SPAM
November 19, 2006 at 9:11 am
I AM ALSO TRYING THE SAME BUT NOT BE ABLE TO GET IT.. BUT I HAVE AN IDEA YOU CAN USE ENVIOREMENT VARIABLE.. IF YOU ARE DONE WITH THIS PLEASE LET ME KNOW.
February 4, 2008 at 12:55 pm
I had a similar problem when running... and I had to change the following from this.
C:\>dtexec /SQL \MultipleVersionsTest /Set \package.variables[ConfigConn].Value;
Data Source=DB1;Initial Catalog=Testing;Provider=SQLNCLI.1;Integrated
Security=SSPI;Auto Translate=False;
to this to get it to work.
C:\>dtexec /SQL \MultipleVersionsTest /Set \package.variables[ConfigConn].Value;
\""Data Source=DB1;Initial Catalog=Testing;Provider=SQLNCLI.1;Integrated
Security=SSPI;Auto Translate=False;"\"
February 5, 2008 at 8:42 am
This was my solution to my original issue and has been working very good for almost a year and a half now. Note that my package is stored on the file system.
DTEXEC /FILE \\put your complete UNC path and the package name here /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /Decrypt put your package password here if there is one /SET \Package.Variables[User::your package variable name here].Properties[Value];put the value for the package variable here /SET \Package.Connections[put the package connection name here].ServerName;put the name of the server instance that you want the package to run in here
December 11, 2008 at 3:32 pm
Great find, putting "\" before and after my value fixed it for me.
December 11, 2008 at 8:08 pm
You can also use package configuration and store that config in XML File and update that file at runtime.
September 10, 2009 at 9:10 am
dtexec.exe /sql \AADDM\AADDM_Load_USPL /SERVER "130.175.234.224" /USER ssisexec /PASSWORD ssisexec703 /set \Package.Connections[ADDM].Properties[ConnectionString];\""Data Source=xx;User ID=xx;Initial Catalog=xx;Provider=SQLNCLI.1;Persist Security Info=True;\""
May 1, 2010 at 6:29 am
Great Tip. Thanks Guys! This saved me mucho time this morning. SQLServerCentral Rocks!
June 3, 2011 at 11:24 am
THANK YOU! After wasting nearly half a day toying with escape characters and browsing dozens of sites with useless help this one finally worked to pass in a connection string value.
Syntax that worked:
/f package.dtsx /SET \Package.Connections[***].Properties[ConnectionString];\""Data Source=***;initial catalog=***;Provider=SQLNCLI10.1;Integrated Security=SSPI;"\"
September 28, 2011 at 12:02 am
Robert Cain-464398 (12/11/2008)
Great find, putting "\" before and after my value fixed it for me.
putting "\" before and after my value worked for me as well.
October 17, 2011 at 2:36 am
PAram-383796 (12/11/2008)
You can also use package configuration and store that config in XML File and update that file at runtime.
I have a similar requirement here. I have an xml configuration file with around 20 variables. I've created a sql agent job to run the ssis package using this configuration file, but I want the agent job to run by a schedule. One of the configuration file variables, EndDate, I want to set to the current scheduled run date and time of the agent job. In other words, change the xml configuration file at runtime as you say here. How would I do this though please? Do you have an example for me please? With 20 variables in the configuration file, I don't really want to write something to set all 20 variables if I can help it.
March 19, 2015 at 9:51 am
Package is password procted, i had change connection windows autetication
/FILE "c:\Packge.dtsx"
/DECRYPT /CONNECTION "Server.user";"\"Data Source=server;
User ID=user;Provider=SQLOLEDB.1;Persist Security Info=True;
Application Name=SSIS-Package-{DD33AC67-4A45-40D6-AF70-4BBD421931C1}BPOSQLDB01\BPOSQLDB01.log4BPO;
Auto Translate=False;\"" /CONNECTION "server.conn 1";
"\"Data Source=server;User ID=User;Initial Catalog=db;
Provider=SQLOLEDB.1;Persist Security Info=True;Auto Translate=False;
Application Name=SSIS-Package-{665E3825-6AFC-4DD3-ABC8-50B5F0F18EEB}server.conn 1;
\"" /CHECKPOINTING OFF /REPORTING E
Waht sholud be changed ?
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply