Running the same SSIS package with different parameters at different times

Question

I have an SSIS package that runs at 8pm in the evening in 2011.

I would like to launch the same package at 8:30 pm for 2010.

I created the SSIS package configuration file and accepted "Year" as a parameter. Whenever I run, I need to open the file, change the value and run it.

Can I schedule and set the dynamic value of Year?

Or use 2 different configuration files - the only way to solve it?

Thanks to everyone.

Answer 1

The problem with using the approach to the configuration file is that you will need to constantly modify the file. SSIS does not reload the configuration file after it is run, so you could have 8:05 and 8:35 PM work orders that change the configuration files, but at some point they will be tangled and broken.

I would handle this situation with command line variables ( / set option in dtexec ). If you ran the package from the command line, it would look like dtexec.exe /file MyPackage.dtsx . Even if you use SQL Agent, these command line arguments are built behind the scenes.

This approach assumes that you are creating two different tasks (vs 1 task is scheduled 2 times a day). AgentMyPackage2011 has an SSIS job phase that results in

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

and AgentMyPackage2012 has an SSIS job step that results in

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

Through the graphical interface, it will look something like this:

There is no GUI or selector for the property you want to configure. However, since you have already created the .dtsConfig file for your package, open this file and find the section, for example

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

The file already has a path to the “thing” that you are trying to configure in such a way as to include this program in your calling program, and then disconnect part of the package configuration for a year.

Finally, a link to SSIS Configuration Priority , as there are differences in the 2005 versus 2008 model. I see that you indicated 2008 on your ticket, but for future readers, if you use both / SET and the configuration source (xml, sql server, registry, environment variable), the order of operations depends on the versions.