Run SSIS Package with 2 Different Configurations

Question

We have a SSIS job called ExportData and it accepts the 'ExportType' Parameter. The ExportType parameter can be 'schedule' or 'unschedule'.

I created the Variable called '@ExportType' and created the SSIS Configuration and expose the variable in the Configuration file and called it 'ScheduleConfig'. I copied that file and change the value to 'unschedule' and called it 'UnscheduleConfig'.

I created the SSIS Job in the SQL Server 2008 and set up 2 steps for both 'Schedule' and 'Unschedule'. I attached the correct config file for each Step. But whichever step I run, it always execute 'Schedule'. I am sure and double checked the Config files and Steps.

How can I run 2 different jobs with 2 different configuration files?

I did try by using the SetValues method and it doesn't work too.

Solution 1

I suggest you not store @ExportType in an SSIS Configuration. You can override the value from the DTEXEC command line by adding a SET switch similar to:

/SET "\Package.Variables[ExportType].Properties[Value]";Schedule

You can use the same PackagePath above to override this variable value in the SQL Agent Integration Services Job Step Type on the Set Values tab.

Hope this helps, Andy

Solution 2

you cant do that. The SSIS wont read the new configuration.

You need to call the package twice, one time with config file A and one time with config file B. On each config file you will have the @ExportType variable set to 'schedule' and 'unschedule'.

You can only set a parameter once. Even if you use DTEXEC with the /Config option for example you CANT overwrite a parameter that is already set