sql server 2008 - Executing the same SSIS Package with different parameters at different time

Question

I have a SSIS package running at 8 PM in the evening for the Year 2011.

I would like to run the same package at 8:30 PM for the Year 2010.

I made a SSIS Package configuration file and accept the "Year" as a parameter. Whenever I run, I need to open a file, change the value and run it.

Is it possible to set up the schedule and set the Year value dynamically?

Or using 2 different configuration file is the only way to solve it?

Thanks all.

Answer 1

The challenge with using a configuration file approach is that you would need to constantly modify the file. SSIS wouldn't reload the config file after it starts so you could conceivably have 8:05 and 8:35 PM jobs that swaps config files but that's going to get messy and break at some point.

I would handle this situation with command line variables (/set option in dtexec). If you were running the package from the command line, it'd look something like dtexec.exe /file MyPackage.dtsx Even if you're using SQL Agent, behind the scenes it's building those command line arguments.

This approach assumes you create two different jobs (vs 1 jobs scheduled 2x a day). AgentMyPackage2011 has a job step of SSIS that results in

• dtexec /file MyPackage.dtsx /Set Package.Variables[User::Year].Properties[Value];"2011"

and AgentMyPackage2012 has a job step of SSIS that results in

• dtexec /file MyPackage.dtsx /Set Package.Variables[User::Year].Properties[Value];"2012"

Through the GUI, it'd look something like

There is no GUI or selector for the property you wish to configure. However, since you've already create a .dtsConfig file for your package, open that file up and look for a section like

<Configuration ConfiguredType="Property" Path="Package.Variables[User::Year].Properties[Value]" ValueType="Int32">
<ConfiguredValue>2009</ConfiguredValue>

The file already has the path to the "thing" you are attempting to configure so punch that into your calling program and then turn off the year portion of package configuration.

Finally, a link to SSIS Configuration Precedence as there are differences in the 2005 vs 2008 model. I see you indicated 2008 in your ticket but for future readers, if you're using both /SET and a configuration source (xml, sql server, registry, environment variable) the order of operations varies between versions.