Changing SQL Server Agent settings on the fly?

Hello all,

I have created a simple package that imports data from a flat file into a database. To run the package I'm using a SQL Server Agent Job. The location of the file is stored as a connection string in the Connection Managers tab in the SQL Server Agent Job.

Is there a way to change this connection string programmatically? If not programmatically, is there a way to change this setting right before I execute the package. I want to change the location of this file based on user input. Also, I'm executing the package using the sp_start_job stored procedure to run the job.

Thanks in advance for any advice!

-Dwayne

You could just create the entire job on the fly, they can be scripted in T-SQL.

If you want to keep the job, then you could use sp_update_jobstep to update the step command. Now I have not looked at the command for the SSIS subsystem, but I'd recommend use the CmdExec subsystem and calling DTEXEC myself, the logging is much better. Even MS recommend that approach in one of their KB's now. BTW could read it here first, illustrates why - http://wiki.sqlis.com/default.aspx/SQLISWiki/ScheduledPackages.html

Finally leave the job alone, and store the value in a table. You could manually use the Exec SQL Task to query the value, or perhaps better still use a SQL server based configuration. Have a look in Books Online about configurations, they are very useful, and I suggest you use them anyway to help manage connection details.

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 :)

Change the database name in the ConnectionString property instead.|||

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.

|||You may want to read through this post:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1043984&SiteID=1|||

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?

|||I think, you should surround each part of the SET argument in double quotes. Embedded quotes can (should?) be escaped.

/SET "parameter-to-set";"value-with-\"escaped double quotes\""

So, try:

/SET "\Package.Connections[DestinationConnectionOLEDB].Properties[ConnectionString]";"Data Source=(local);Initial Catalog=TestDB;Provider=SQLNCLI;Integrated Security=SSPI;Auto Translate=false;"|||

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

|||Try this instead of /SET... This should work, because the Execute Package Utility generated it for me:

/CONNECTION DestinationConnectionOLEDB;"\"Data Source=(local);Initial Catalog=TestDB;Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;\""|||

Thanks Phil!

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

Cheers

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 :)

Change the database name in the ConnectionString property instead.|||

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.

|||You may want to read through this post:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1043984&SiteID=1|||

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?

|||I think, you should surround each part of the SET argument in double quotes. Embedded quotes can (should?) be escaped.

/SET "parameter-to-set";"value-with-\"escaped double quotes\""

So, try:

/SET "\Package.Connections[DestinationConnectionOLEDB].Properties[ConnectionString]";"Data Source=(local);Initial Catalog=TestDB;Provider=SQLNCLI;Integrated Security=SSPI;Auto Translate=false;"|||

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

|||Try this instead of /SET... This should work, because the Execute Package Utility generated it for me:

/CONNECTION DestinationConnectionOLEDB;"\"Data Source=(local);Initial Catalog=TestDB;Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;\""|||

Thanks Phil!

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

Cheers