Passing connection string properties when using DTEXEC

2

I am wondering what the correct format is for passing connection string properties on the command line when using dtexec:

dtexec.exe /Ser IpAddress\Instance /IS "\SSISDB\Data Warehouse\MyProject\MyPackage.dtsx" /DumpOnError /Set \Package.Variables[DW_ConnectionString].Properties[Value];\""Data Source=IpAddress;Initial Catalog=DWDB;Provider=SQLNCLI10.1;IntegratedSecurity=SSPI;"\"

I have defined the above command line configuration where I am attempting to pass override properties for the default connection string properties. The packages I'm targeting are not using package connections, but instead project level parameters/properties have been defined to store the DB connections. For some reason I can't get this to work . I am getting an error message on the server saying

Failed to configure an overridden property that has the following path: \Package.Variables [DW_ConnectionString].Properties [Value]. An error occurred while setting the value of property "Value". The error returned is 0x80020009

Is my format correct for overriding properties? The packages are hosted on a remote server

ssis
sql-server-2012
asked on Stack Overflow Jul 18, 2013 by TGH • edited Jul 18, 2013 by billinkc

2 Answers

4

Try using DTEXECUI next time to generate your command string. It has places for all of the variables, connection managers etc. All you have to do is bring up your package and it fills everything in. You then enter any changes you want in the GUI then go to the Command line tab and it will give you the string to put after DTEXEC.EXEC. You can of course also run the package from DTEXECUI also.

enter image description here

answered on Stack Overflow Jul 18, 2013 by Kenneth Fisher
3

It turns out that my format was wrong: It's incorrect to use /Set Package Variable in this context:

The correct format is:

/Par "$Project::DW_ConnectionString";\""Data Source=Server\Instance;Initial Catalog=myDb;Provider=SQLNCLI11.1;Integrated Security=SSPI;AutoTranslate=False;"\"
answered on Stack Overflow Jul 18, 2013 by TGH

User contributions licensed under CC BY-SA 3.0