Changing destination database for SSIS Package

Hi,

I have a small problem. I've gone through the SSIS wizard and created a dtsx file which imports data from an access file into a SQL Server 2005 database. It has been set to delete existing rows and enable identity insert.

I then edited the .dtsx package in SQL Server Management Studio and added an environment variable configuration to allow me to change the destination database. In the script which runs the dtsx, here is what I have (it's an x64 system, so hi have to use DTExec):

"C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn\DTExec.exe" /file e:\testimport.dtsx /set \Package.Connections[DestinationConnectionOLEDB].Properties[InitialCatalog];newdatabasename

and here is the error I get:

Description: The configuration environment variable was not found. The envir
onment variable was: "InitialCatalog". This occurs when a package specifies an e
nvironment variable for a configuration setting but it cannot be found. Check th
e configurations collection in the package and verify that the specified environ
ment variable is available and valid.

I got the package.connections.etc etc path from originally creating the environment variable as an xml config file, then I could open the config file and see what the path was...

Any help would be appreciated :)

Can you help me with the syntax for that Do I have to create an environment variable for connectionstring instead of initialcatalog

I've tried this:

E:\>"C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn\DTExec.exe" /file e:\testimport.dtsx /set \Package.Connections[DestinationConnectionOLEDB].Properties[ConnectionString];"Data Source=(lo
cal);Initial Catalog=TestCo_T_TWC;Provider=SQLNCLI;Integrated Security=SSPI;Auto
Translate=false;"

And i guess it doesn't like the quotes or something, because it causes a syntax error, but when i try it without quotes, i get:

option "Source=(local);Initial" is not valid.

Hi,

Thanks for the help. I read through that thread and it is very close to answering my question. What I don't get though is how to punctuate the connection string. In the examples from the other post, they didn't have a long connection string with spaces in it, so it didn't have any punctuation (or the ones that did were the ones that were causing the OP trouble, so it's hard to say if his punctuation was correct). Can you help me with where to put my quotation marks please

E:\>"C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn\DTExec.exe" /file e:\testimport.dtsx /set \Package.Conne
ctions[DestinationConnectionOLEDB].Properties[ConnectionString];"Data Source=(local);Initial Catalog=TestDB;Provider=SQLNCLI;Integrated Security=SSPI;Auto Translate=false;"

The above gives me the error:

Argument ""\Package.Connections[DestinationConnectionOLEDB].Properties[ConnectionString];Data Source=(local);Initial Catalog=TestDB;Provider=SQLNCLI;Integrated Security=SSPI;Auto Translate=false;"" for option "set" is not valid.

You can see it appears to have taken my quotes from around the connection string and put them around the entire /set argument... So how do I fix that

Hi Phil,

Thanks for your help. That didn't work either, it leads to the same error as before:

Argument ""\Package.Connections[DestinationConnectionOLEDB].Properties[Connectio
nString];Data Source=(local);Initial Catalog=TestDB;Provider=SQLNCLI;Integ
rated Security=SSPI;Auto Translate=false;"" for option "set" is not valid.

Which is wierd because it is ignroing the double quotes I put between the package.connections etc etc and the data source= etc etc

Thanks Phil!

It's too late for me to name my firstborn after you, but I still really appreciate your help today!

Cheers

Hi,

I am also facing the same problem for connection string for excel. Have you got the solution