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.