RE: Multiple environments on the same server

Using user name instead of (or in addition to) host name is probably the easiest way to handle having multiple environments on the same server.

The other connection-specific function is APP_NAME(), but I haven't thought of a simple way to make use of it. The SSIS_Config view could be rewritten to look for the environment setting in APP_NAME(), then you would need a configuration item that sets the SSIS_Config connection manager ApplicationName property before any of the SQL configuration items. Or you could use a parent package that uses a commandline config setting to define a variable, then use parent package configuration to copy that value to ApplicationName. That's too many layers of indirection for me though.

RE: Hassles about querying production server from test

I had a developer who kept bringing this up. Since the package configuration process takes place before the package starts, does not involve user code, and is read-only, I don't consider it a violation of the separation of environments. Neither of us changed the other's mind, but my opinion was the only one that mattered.

But if you're in an rigid environment where the complaints are coming from someone with the authority to enforce them, you have to address it.

One idea would be to put the configuration database on its own server, maybe just a simple SQL Express instance. This would have to be a server visible to all your package-execution servers, but if the powers-that-be can accept that it is a configuration server and not part of prod/test/dev then maybe they will go along with it.

If you are forced to break the configuration into multiple databases, while still being able to deploy packages with no changes, you could use an environment variable config item to set the ServerName property of the SSIS_Config connection before any SQL configuration items are used.

Another way would be to have the network admins define a domain alias that points to the dev config server, and use this name in all your packages. On the package-executing servers that need production config (and also test servers if you need three separate config dbs), you can define an overriding local alias in the hosts file or with a SQL Server Configuration network client alias.

To simplify maintenance you could set up a configuration maintenance database that uses a partitioned view to pull the pieces back together and make it look like a single table. An ideal location would be on your laptop or some other server under your control that won't start another multi-environment argument.

Getting the partitioned view set up properly requires some changes to all SSIS_Config_base tables.

First you need to replace the clustered primary key (ConfigId) of each SSIS_Config_base table with a clustered unique index on ConfigId and a nonclustered primary key on (ConfigId, EnvironmentEnum).

Then modify the CK_SSIS_Config_EnvironmentRangeCheck constraint on each base table to limit EnvironmentEnum to the subset used on that server.

Now you need a linked server from your config maintenance server to all the separate config servers, and you can define your partitioned view with

CREATE VIEW dbo.SSIS_Config_Base AS

SELECT * FROM ProdServer.SSIS_Config.dbo.SSIS_Config_base

UNION ALL

SELECT * FROM TestServer.SSIS_Config.dbo.SSIS_Config_base

UNION ALL

SELECT * FROM DevServer.SSIS_Config.dbo.SSIS_Config_base

The existing SSIS_Config_AllEnv view will work just fine with this base view and pull the separate parts back together. The partitioned view should be updatable because of the primary key and constraint changes listed above, so the SetValues and SplitEnvironments procedures should also work. The MergeEnvironments procedure is a problem because global settings (EnvironmentEnum=0) will only exist in one environment. To fix this and also handle any ad-hoc queries, you could define an INSTEAD OF trigger on the partitioned view to make sure global value changes are replicated to the other dbs. This would handle changes made in the maintenance database, but there's no good way to handle new global configurations created in BIDS. They will be created on the dev config server, and it won't be able to write them to prod. They will have to be fixed before the package can be deployed.

The last step would be to modify the Config_Inconsistent view so it considers global + production settings to be required rather than an exception. All procedures and views (except SSIS_Config) should be removed from the config databases and exist only in the maintenance database.

Feel free to take this list of all the hoops you will have to jump through with you when you reopen the discussion of whether the SSIS configuration scheme should be exempted from cross-environment prohibitions. Play up the notion of needing a domain alias and access to production hosts files for extra leverage.