SSIS multi-environment configuration in a single SQL Server table

  • Scott Coleman

    One Orange Chip

    Points: 27442

    Comments posted to this topic are about the item SSIS multi-environment configuration in a single SQL Server table

  • Frank Bazan

    SSCertifiable

    Points: 5348

    Thats a really neat approach. Good article.

    Kindest Regards,

    Frank Bazan

  • Andy Leonard

    SSChampion

    Points: 10023

    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, Chief Data Engineer, Enterprise Data & Analytics

  • Mike DiRenzo

    SSCrazy

    Points: 2013

    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

    SSC Journeyman

    Points: 89

    Any chance you can post sample of your technique to swap connections based on environment dynamically.

    Thanks

  • Scott Coleman

    One Orange Chip

    Points: 27442

    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

    One Orange Chip

    Points: 27442

    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

    SSCertifiable

    Points: 6019

    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.

  • Scott Coleman

    One Orange Chip

    Points: 27442

    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

    SSChampion

    Points: 13157

    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.

  • Bradley Deem

    SSCrazy

    Points: 2565

    I recently posted about Package Configurations throwing Warnings rather than Errors. I don't suppose you've solved that problem as well? My current solution treats all warnings as errors.

    http://www.sqlservercentral.com/Forums/Topic724499-148-1.aspx#bm724516

  • DavidSimpson

    Hall of Fame

    Points: 3551

    Well written article and new way of working with Configuration tables. I like the trigger used to track changes in the configuration table. To me, though, the data and security risks of having all the configurations in one table far out-way a little extra work on my part to manage configuration tables by application and server. I require the separation of production, QA and test data and even separation of data by application. I actually require that the configuration tables be loaded into a separate (non-dbo) schema. This allows me to easily control security by schema. All of this allow the developers to access their own configuration tables for testing but cannot even see the QA and production configuration tables... nor can they see the configuration table for other applications.

    The migration of configuration data from test to QA and prod has to go through the DBA, which then modifies the data as needed to update passwords, folders, etc. From what I've seen, configurations are not changed that often so once they are set up, the maintenance and SSIS migrations are fairly simple. We use environmental variables to define which server and development environment the package is running in. This is easy to set up and simple way to allow developers to jump between different development environments.

    David

  • John Rowan

    SSC Guru

    Points: 56440

    Great article Scott. I love your approach. Just curious, what are you using for your Package Protection Level, server storage?

    John Rowan

    ======================================================
    ======================================================
    Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

  • Scott Coleman

    One Orange Chip

    Points: 27442

    John Rowan (6/3/2009)


    Just curious, what are you using for your Package Protection Level, server storage?

    I prefer to use the DontSaveSensitive setting, because I may want to deploy packages to a file location rather than a server. All passwords are going to be configured, so this is not a limitation.

    In our environment, our security needs are satisfied by limiting rights on the configuration table to the developers, SQL Server, and SQL Server Agent logins. We have no reason (so far) to restrict some settings to only certain developers, DBAs, or sysadmins, although I appreciate that other people in larger teams may have such problems.

  • Misha_SQL

    SSCertifiable

    Points: 5388

    Great article. I think Microsoft should built in something like this into the next release of SQL Server (and pay royalties to Scott ;-))

Viewing 15 posts - 1 through 15 (of 58 total)

You must be logged in to reply to this topic. Login to reply