Executing the same SSIS Package with different parameters at different time

sql-server-2008 parameters ssis sql-server-agent
TTCG picture TTCG · Dec 5, 2011 · Viewed 33k times · Source

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

billinkc picture billinkc · Dec 5, 2011

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 SQL Agent Set values tab

SQL Agent Command line tab

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.

Related questions

How to set table name in dynamic SQL query?

I want to set table name in a dynamic SQL query. I tried successfully for parameter as following: /* Using sp_executesql */ /* Build and Execute a Transact-SQL String with a single parameter value Using sp_executesql Command */ /* Variable Declaration */ DECLARE @EmpID …

Read More
Null value in a parameter varbinary datatype

How can I add a null value in a parameter varbinary datatype? When I execute the following code: using (SqlConnection myDatabaseConnection1 = new SqlConnection(myConnectionString.ConnectionString)) { using (SqlCommand mySqlCommand = new SqlCommand("INSERT INTO Employee(EmpName, Image) Values(@EmpName, @Image)", myDatabaseConnection1)) { mySqlCommand.…

Read More
How can I get column names from a table in SQL Server?

I want to query the name of all columns of a table. I found how to do this in: Oracle MySQL PostgreSQL But I also need to know: how can this be done in Microsoft SQL Server (2008 in my case)?

Read More