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 Wednesday, June 3, 2009 12:07 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, July 18, 2014 12:13 PM
Points: 2,837, Visits: 1,138
Comments posted to this topic are about the item SSIS multi-environment configuration in a single SQL Server table


Post #727890
Posted Wednesday, June 3, 2009 3:09 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 3:24 AM
Points: 268, Visits: 1,073
Thats a really neat approach. Good article.

Kindest Regards,

Frank Bazan
Post #727993
Posted Wednesday, June 3, 2009 6:01 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, July 21, 2014 12:44 PM
Points: 389, Visits: 1,041
Hi Scott,

Great article and excellent use of SSIS Package Configurations. I really like centralizing the application tier (Dev, Test, Prod) selection at the configuration server. Elegant solution.

How would you apply this to environments with firewalls between application tiers? I've used a two-step approach with an environment variable that points to a configuration database for individual tiers.

Andy


Andy Leonard
CSO, Linchpin People
Follow me on Twitter: @AndyLeonard
Post #728074
Posted Wednesday, June 3, 2009 6:13 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, February 25, 2014 11:28 AM
Points: 145, Visits: 193
Scott:

Excellent article! Your insight to the configuration issue is spot on and brings with it a tremendous amount of merit.

When you mentioned that you started with the group and discovered all the great opportunities to improve things (newly ported DTS packages to SSIS with no CFG file), I thought about how you went about deriving your clever solution and the angst from your peers and management in terms of your future implementation plan for integrating. I wonder how tough of a "sell" it was to your peers and management. This is ALWAYS where the rubber meets the pavement...delivery of the concept is key and requires a grass roots or bottom up sell.

It seems this is where some of us get into trouble when conveying a solution to a problem and managing the push-back from others. Your solution is obvisouly clever and in my opinion extremely well-thought out. Your solution here is a testament to your future success.

Kudos.

-M
Post #728079
Posted Wednesday, June 3, 2009 6:25 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, June 4, 2013 4:53 PM
Points: 1, Visits: 46
Any chance you can post sample of your technique to swap connections based on environment dynamically.
Thanks
Post #728087
Posted Wednesday, June 3, 2009 6:44 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, July 18, 2014 12:13 PM
Points: 2,837, Visits: 1,138
Andy Leonard (6/3/2009)
How would you apply this to environments with firewalls between application tiers? I've used a two-step approach with an environment variable that points to a configuration database for individual tiers.

I considered this issue, and that is the best approach I could think of. You need a separate configuration table for each isolated network, but you still have the benefit of only needing one version of the package that can be deployed anywhere without modification.



Post #728113
Posted Wednesday, June 3, 2009 6:52 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, July 18, 2014 12:13 PM
Points: 2,837, Visits: 1,138
Mike DiRenzo (6/3/2009)
I wonder how tough of a "sell" it was to your peers and management. This is ALWAYS where the rubber meets the pavement...delivery of the concept is key and requires a grass roots or bottom up sell.

It wasn't a tough sell because there weren't a lot of ingrained bad practices to change, and because I work with people intelligent enough to recognize a better approach. (And who may read these comments. )



Post #728124
Posted Wednesday, June 3, 2009 6:59 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: 2 days ago @ 11:40 AM
Points: 282, Visits: 2,209
Really good article. I'm going to adapt my current configuration table to use a trigger similar to yours since I hadn't thought of that.


MCITP, Database Administrator
A hodgepodge of Information Technology and Life
LinkedIn Profile
My Twitter
Post #728134
Posted Wednesday, June 3, 2009 7:10 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, July 18, 2014 12:13 PM
Points: 2,837, Visits: 1,138
Simon Storey (6/3/2009)
Any chance you can post sample of your technique to swap connections based on environment dynamically.

The article describes a way of managing multiple package configurations easily, it assumes the reader already has some knowledge of configuration usage. The example package in the article only modifies some package variable values, but it works the same way for connection manager properties, task properties, or anything else you want to configure.

1. Create a package with a connection manager.
2. Use package configuration to put the connection manager properties in a SQL table.
3. Use the techniques in the article to supply multiple values.

The SSISConfiguration.sql file linked under "Resources" in the article has all the code.



Post #728148
Posted Wednesday, June 3, 2009 7:44 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Thursday, July 17, 2014 10:56 AM
Points: 3,924, Visits: 1,607
Very good article and a very comprehensive approach, Scott. Was using same tech for single server but had not for multi-environment. Would definitely try out in near future.

Again, thanks for the sql configuration.sql


SQL DBA.
Post #728188
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse