<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
Article Discussions
»
Article Discussions by Author
»
Discuss content posted by Scott Coleman
»
SSIS multi-environment configuration in a...
47 posts, Page 1 of 5
1
2
3
4
5
»
»»
SSIS multi-environment configuration in a single SQL Server table
Rate Topic
Display Mode
Topic Options
Author
Message
Scott Coleman
Scott Coleman
Posted Wednesday, June 03, 2009 12:07 AM
SSCrazy
Group: General Forum Members
Last Login: Tuesday, May 21, 2013 1:39 PM
Points: 2,818,
Visits: 1,038
Comments posted to this topic are about the item
SSIS multi-environment configuration in a single SQL Server table
Post #727890
Frank Bazan
Frank Bazan
Posted Wednesday, June 03, 2009 3:09 AM
SSC Veteran
Group: General Forum Members
Last Login: Wednesday, March 06, 2013 3:02 PM
Points: 268,
Visits: 1,055
Thats a really neat approach. Good article.
Kindest Regards,
Frank Bazan
@Bikeride2Africa
Post #727993
Andy Leonard
Andy Leonard
Posted Wednesday, June 03, 2009 6:01 AM
Old Hand
Group: General Forum Members
Last Login: Monday, May 13, 2013 2:53 PM
Points: 375,
Visits: 947
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
Mike DiRenzo
Mike DiRenzo
Posted Wednesday, June 03, 2009 6:13 AM
SSC-Enthusiastic
Group: General Forum Members
Last Login: Wednesday, April 24, 2013 12:11 PM
Points: 143,
Visits: 183
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
Simon Storey
Simon Storey
Posted Wednesday, June 03, 2009 6:25 AM
Forum Newbie
Group: General Forum Members
Last Login: Monday, May 13, 2013 11:55 AM
Points: 1,
Visits: 44
Any chance you can post sample of your technique to swap connections based on environment dynamically.
Thanks
Post #728087
Scott Coleman
Scott Coleman
Posted Wednesday, June 03, 2009 6:44 AM
SSCrazy
Group: General Forum Members
Last Login: Tuesday, May 21, 2013 1:39 PM
Points: 2,818,
Visits: 1,038
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
Scott Coleman
Scott Coleman
Posted Wednesday, June 03, 2009 6:52 AM
SSCrazy
Group: General Forum Members
Last Login: Tuesday, May 21, 2013 1:39 PM
Points: 2,818,
Visits: 1,038
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
Jason Crider
Jason Crider
Posted Wednesday, June 03, 2009 6:59 AM
SSC Veteran
Group: General Forum Members
Last Login: Yesterday @ 10:08 AM
Points: 282,
Visits: 2,126
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
Scott Coleman
Scott Coleman
Posted Wednesday, June 03, 2009 7:10 AM
SSCrazy
Group: General Forum Members
Last Login: Tuesday, May 21, 2013 1:39 PM
Points: 2,818,
Visits: 1,038
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
SanjayAttray
SanjayAttray
Posted Wednesday, June 03, 2009 7:44 AM
Hall of Fame
Group: General Forum Members
Last Login: Friday, March 15, 2013 2:43 PM
Points: 3,924,
Visits: 1,554
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 »
47 posts, Page 1 of 5
1
2
3
4
5
»
»»
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
