Can One SSIS Package use two different XML Config files in the same execution?

2
votes

I've inherited some SSIS Packages that need to be modified for a SQL 2008 R2 to SQL 2012 migration. Unfortunately, the "configuration" was done rather poorly.

We have a global XML Config file for all SSIS Packages, and just a few variables in the XML Config file--Server Name, ODS Server Name, and Environment (Development, Integration, PreProd, and Production).

To "configure", they write code in the SSIS Package: If 'Development' Then ... Else If 'Integration' Then ...

In order to change the "configuration", one has to change the code in the Package.

I have unsuccessfully tried to negotiate a change, but no one is budging so the one XML Config file remains.

If I can add a second XML Config file, with my Package specific variables that need to be configured in each Environment, that's what I will do. However, I have not found a way to do this. Is is possible?

My second choice is put variables in a SQL Server table.

1
ssissql-server-2008-r2sql-server-2012ssis-2012
Wait, what? In order to change the "configuration", one has to change the code in the Package Are they manually parsing the config file and assigning values in a script task? Otherwise, once the package starts executing all the configuration is already done - billinkc
I am happy to stake all of my magical SO unicorn points and my gold SSIS badge and unequivocally state: they're doing it wrong. - billinkc
You've probably discovered that SQL 2012 has a new feature 'environments' (using the project deployment model). Perhaps you could argue that you are not taking full advantage of the new features - Nick.McDermaid
PS this needs to be posted to thedailywtf.com - Nick.McDermaid
@Nick.McDermaid I looked like a damn genius when I went to a client in 2007. They were using the machine.config for their IIS so might as well use it for SSIS too, right? So every package had mostly the same code to parse it and grab the required connection strings. "Y'all know this is built into the product, right?" crickets - billinkc

1 Answers

1
votes

I think your best bet is to replace all of your script tasks with OLE DB Commands to call a stored procedure which would take the following parameters: Environment, ConnectionName, PackageName, and ServerType(standard or ODS). The output of this stored procedure would be the ServerName, which would be assigned to a variable. This variable could be then be used to set the server name for the connection. The stored procedure could depend on table(s) or global XML file(s). I would suggest tables. Either way, the logic in the package would be minimal and would allow you to implement in whatever way you see fit.