Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««12345

SSIS multi-environment configuration in a single SQL Server table Expand / Collapse
Author
Message
Posted Saturday, August 20, 2011 11:10 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, November 4, 2014 1:50 PM
Points: 2,845, Visits: 1,160
(Sorry for the late response, I've been ignoring my generic email because it's full of junk.)

You can configure the connection for SQL configuration several ways, including environment variables. What's important to keep in mind are the ways you can't configure them.

You can't configure the configuration connection with a command line switch, as this switch will not be processed until after all the configuration settings have been read. Ditto for parent package configurations.

Any other direct configuration technique (environment variable, XML file, a separate SQL configuration) can modify the connection manager to control the source for later SQL configurations.

Expressions on the SQL config connection manager will not be evaluated until after the configuration settings are read, so the connection manager properties must be configured directly and not through a package variable.



Post #1162892
Posted Tuesday, August 30, 2011 6:40 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 5:20 AM
Points: 1,611, Visits: 1,399
No worries on the delay, Scott. I understand how it can be with the deluge of e-mails.

The behavior that you mentioned, not having the ability to use a command-line switch, has changed in SQL 2008 right? You can use the /connection switch to set the connection from which you would obtain configuration values. (http://msdn.microsoft.com/en-us/library/ms141682.aspx)

I work in an environment where we cannot use environment variables and using XML files is not an option either (obtaining the permissions on the network shares for the SQL Server accounts would take FOREVER, if possible!), so we use SQL Server configuration, but with the /connection switch. It's not as robust as your solution, but it works. And I will keep your solution in mind for different environments.

Thanks...Chris
Post #1167451
Posted Thursday, September 15, 2011 5:05 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, March 3, 2014 4:18 PM
Points: 1, Visits: 19
I second the earlier question... How would you apply this to environments with firewalls, or in my case security restrictions, between application tiers? I currently have this set up in my company with Dev, Stage, and Prod all hitting the same Production SSIS_Configurations database, and it is working, but I get constant push-back from engineering on why Stage is hitting a Production table. We used to have two separate SSIS_Configurations databases, one for Prod and one from Dev/Stage, but it was high-maintenance, and to me that seemed to defeat the purpose, which is to manage everything from one table. Has anyone else had this problem?
Post #1176114
Posted Saturday, October 8, 2011 3:25 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, October 8, 2014 4:05 PM
Points: 1, Visits: 111
Hello Scott,
Very useful article.

I've some different scenerio. I've one server where all the SSIS package jobs runs. I would like to have only one SSIS package to run in all the different prod servers. Luckly, this SSIS package is using same database in all the environments.

Your help is appreciated.



Post #1187557
Posted Tuesday, July 24, 2012 12:14 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: 2 days ago @ 2:17 PM
Points: 8, Visits: 604
I know this is an old post, but I have a question about this approach. I have used this in multiple servers in the past with no issues, but my current customer is using the same physical server for dev and qa. This means I get the same HOST_NAME() when running SSIS packages in both environments.

Is these any way to make this work that will allow me to deploy packages and run via SQL agent jobs on both environments? Is there any way for the SSIS package to distinguish between them and return the proper rows in the view?

Thanks,
Kim
Post #1334711
Posted Tuesday, July 24, 2012 12:51 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 12:40 PM
Points: 54, Visits: 145
Hi Kim

I implemented this in an environment with similar constraints. I had QA and PROD on the same server, but was logging in with different user names, so I made the view return values from different environments based on the logged in user,

Regards,
Joon
Post #1334736
Posted Tuesday, September 18, 2012 8:40 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, November 4, 2014 1:50 PM
Points: 2,845, Visits: 1,160
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.



Post #1361103
Posted Friday, July 5, 2013 1:55 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, May 7, 2014 1:53 PM
Points: 1, Visits: 47
Can you provide the code for the stored procedure you are using SSIS_Confit_SplitEnvironments?
Post #1470872
Posted Monday, July 8, 2013 6:52 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, November 4, 2014 1:50 PM
Points: 2,845, Visits: 1,160
There is a link at the end of the article under "Resources" to a .sql file with all the views and procedures.


Post #1471159
« Prev Topic | Next Topic »

Add to briefcase «««12345

Permissions Expand / Collapse