SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SSIS multi-environment configuration in a single SQL Server table


SSIS multi-environment configuration in a single SQL Server table

Author
Message
Scott Coleman
Scott Coleman
Hall of Fame
Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)

Group: General Forum Members
Points: 3850 Visits: 1465
Comments posted to this topic are about the item SSIS multi-environment configuration in a single SQL Server table



Frank Bazan
Frank Bazan
Mr or Mrs. 500
Mr or Mrs. 500 (580 reputation)Mr or Mrs. 500 (580 reputation)Mr or Mrs. 500 (580 reputation)Mr or Mrs. 500 (580 reputation)Mr or Mrs. 500 (580 reputation)Mr or Mrs. 500 (580 reputation)Mr or Mrs. 500 (580 reputation)Mr or Mrs. 500 (580 reputation)

Group: General Forum Members
Points: 580 Visits: 1087
Thats a really neat approach. Good article.

Kindest Regards,

Frank Bazan
Andy Leonard
Andy Leonard
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1172 Visits: 1095
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
Data Philosopher, Enterprise Data & Analytics
Mike DiRenzo
Mike DiRenzo
SSC Veteran
SSC Veteran (279 reputation)SSC Veteran (279 reputation)SSC Veteran (279 reputation)SSC Veteran (279 reputation)SSC Veteran (279 reputation)SSC Veteran (279 reputation)SSC Veteran (279 reputation)SSC Veteran (279 reputation)

Group: General Forum Members
Points: 279 Visits: 210
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
Simon Storey
Simon Storey
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 46
Any chance you can post sample of your technique to swap connections based on environment dynamically.
Thanks
Scott Coleman
Scott Coleman
Hall of Fame
Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)

Group: General Forum Members
Points: 3850 Visits: 1465
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.



Scott Coleman
Scott Coleman
Hall of Fame
Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)

Group: General Forum Members
Points: 3850 Visits: 1465
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. ;-))



Jason Crider
Jason Crider
SSChasing Mays
SSChasing Mays (621 reputation)SSChasing Mays (621 reputation)SSChasing Mays (621 reputation)SSChasing Mays (621 reputation)SSChasing Mays (621 reputation)SSChasing Mays (621 reputation)SSChasing Mays (621 reputation)SSChasing Mays (621 reputation)

Group: General Forum Members
Points: 621 Visits: 2232
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
Scott Coleman
Scott Coleman
Hall of Fame
Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)

Group: General Forum Members
Points: 3850 Visits: 1465
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.



SanjayAttray
SanjayAttray
SSCarpal Tunnel
SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)

Group: General Forum Members
Points: 4437 Visits: 1619
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search