SSIS multi-environment configuration in a single SQL Server table

  • Hi Scott, great articles for the SSIS configuration.

    I was testing your code on my machine and found out that:

    When I try to add a new variable in the package and map it to configuration using BIDS configuration wizard, it will update all the config value from the variables default - This is actually the default behavior. The problem is there will be administrators using BIDS and try to fix something on the production server (don't ask me why - ok the answer is because it's convenient) they will inadvertently reset all the production value to development value settings from the package... This is true happen on file base configuration too, it's just that I got myself into this problem before and luckily I have the config file backup. In your new SSIS config settings we'll need to get it back from history table so writing a restore script might be a good idea.

    My 2 cents is "Do not try to add configuration changes on production server using BIDS."

    Cheers! 😉

  • sqlJunkies2 (6/9/2009)

    they will inadvertently reset all the production value to development value settings from the package

    This is a good argument for using a lot of small configuration sets, to limit the damage.

    You could also use a trigger to reject UPDATEs that didn't come from a "SQL Server Management Studio" connection. Then new configuration sets could be created in BIDS, but updates would have to be done using the stored procs.

  • I have been toying with using this sort of thing in our environment. One problem I ran into was the using the stored procs to update the table and figuring out the best way to automate it as much as possible for dba's or developers.

    I came up with this script that will generate the EXEC statement based on it being one environment. You just have to put in your ConfigurationFilter. It works for me but feel free to add or take away.

    --Generate SQL for setting the values

    --EXEC dbo.SSIS_Config_SetValues 'Filter',N'Path',N'New value'


    DECLARE @sql varchar(1000), @filter varchar(1000), @rc int, @PackagePath varchar(1000)

    --insert your filter here

    SET @filter = 'InsertYourConfigurationFilterHere'

    SELECT ConfigurationFilter, PackagePath

    INTO #Config

    FROM dbo.SSIS_Config_base


    ConfigurationFilter = @filter

    SELECT @rc = 1, @PackagePath = MIN(PackagePath)

    FROM #Config

    WHILE @rc 0


    --dynamic sql to generate the statements

    --EXEC dbo.SSIS_Config_SetValues 'Filter',N'Path',N'New value'

    SELECT @sql = ''

    SELECT @sql = @sql + 'EXEC ' + 'dbo.' + 'SSIS_Config_SetValues '

    SELECT @sql = @sql + '''' + @filter + '''' + ',N' + '''' + @PackagePath + ''''

    SELECT @sql = @sql + ',N' + '''' + 'newvalue' + ''''

    PRINT (@sql)

    SELECT TOP 1 @PackagePath = PackagePath

    FROM #Config

    WHERE PackagePath > @PackagePath

    ORDER BY PackagePath

    SET @rc = @@ROWCOUNT


    DROP TABLE #Config

  • That's an interesting idea. I think I would have it script out the command with the current values:

    DECLARE @filter NVARCHAR(100)

    SET @filter = N'InsertYourConfigurationFilterHere'


    N'EXEC dbo.SSIS_Config_SetValues N''' + ConfigurationFilter + ''',|N''' + PackagePath + ''''

    + CASE WHEN GlobalValue IS NULL THEN '' ELSE ',|@AllValues = N''' + GlobalValue + '''' END

    + CASE WHEN ProductionValue IS NULL THEN '' ELSE ',|@ProdValue = N''' + ProductionValue + '''' END

    + CASE WHEN DevelopmentValue IS NULL THEN '' ELSE ',|@DevValue = N''' + DevelopmentValue + '''' END

    + CASE WHEN TestValue IS NULL THEN '' ELSE ',|@TestValue = N''' + TestValue + '''' END, '|', CHAR(10))

    FROM dbo.SSIS_Config_AllEnv

    WHERE ConfigurationFilter = @filter

  • Would you be willing to share your SSIS template? That could really help the push for SSIS in my space.


  • One more question. I have two databases one for the Test and the other for UAT on the same machine. In which database, do i have to store the SSIS_Config table, and the other views,triggers that you've provided with your script?

    This SSIS Package is stored in MSDB. Does this mean that i have to store the "SSIS_Config" on MSDB also, in order to evaluate the connectionstring if i have to connect to Test Environment or UAT through a common place.

    Thanks and regards,

    Nikos Miliotis

  • As i check the SSIS_Config view, it doesn't support both environments (Development and UAT) to coexist on the same machine. Is there any solution to support this?

  • whug (6/9/2009)

    Would you be willing to share your SSIS template?

    There's nothing special in my template, and you would have to rewrite most of it to suit your environment anyway. The important point is that you want to use a template instead of configuring blank packages from scratch. First figure out how you want to do configuration, logging, and error handling, then create a package with these settings and save it for reuse. The other important point is that they aren't truly templates, at least not in the sense of Office document templates. Word or Excel templates cause a new document to be created, Integration Services simply copies the template file and leaves it up to you to change the Name and ID properties.

    Microsoft has some info on package templates (not nearly as elaborate as the template functionality of other Visual Studio languages)

    Jamie Thomson has a blog entry with a downloadable template package, the next link has an interesting error handling template.

  • Nikos Miliotis (6/9/2009)

    In which database, do i have to store the SSIS_Config table?

    SQL configuration should have its own connection manager, so all packages (in all environments) get configuration settings from the same table (subject to network and/or security constraints). It doesn't have to be in the same database or even the same server as the package storage location or any of the data flow connections. The SQL configuration connection manager can itself be configured (or indirectly configured), so you can have multiple multi-dimensional configuration sets. You can also have more than one configuration connection manager in a package to add more flexibility (and confusion). Basically, the configuration table (including the views and procedures) can be just about anywhere.

    For example, a package might be stored in msdb on server A (or in the filesystem of server B), execute on computer C, get configuration from a table on server D, and operate on data on servers E, F, or G depending on environment. The same package (either loaded from the same location or deployed by a simple copy with no modification) might also execute on computer H and be indirectly configured to get configuration from a table on server J and operation on data on servers K, L, or M.

    A major consideration is what is the most reliable location. If your configuration server is not available, packages will fail. In my environment there is one central SQL Sever that HAS to be available, so this is not a problem. Packages running on other servers will have connections to the main server and will fail anyway if it is down.

  • Nikos Miliotis (6/10/2009)

    As i check the SSIS_Config view, it doesn't support both environments (Development and UAT) to coexist on the same machine. Is there any solution to support this?

    You want me to make it MORE complicated?? :crying:

    In the original article I said it was up to you to define the rules for identifying the different environments. Then you have to rewrite the subquery in the SSIS_Config view to implement your rules. The examples I showed in the article used HOST_NAME() alone to determine the environment, but that's not the only way to do it. The other obvious functions you could use are USER_NAME() and PROGRAM_NAME(). If you have to parameterize it, you could use indirect configuration of the configuration connection string to modify PROGRAM_NAME() (through the AppllicationName property).

    If the rules aren't fixed, one solution might be a table indexed by HOST_NAME() and/or PROGRAM_NAME() that lists the desired environment for each combination. Then you switch from Dev to UAT on the same server by updating the table. Or, if Development is the normal state but you need to occasionally switch to UAT, you could use the example HOST_NAME()-based query to determine the default environment and do a LEFT JOIN with an override table to select UAT.

  • Hi Scott,

    this is a very nice solution. Just one question: How do you manage the connection string for your configuration table? How do you deal with 100+ packages and you have to migrate to another server?


  • If you can't configure all packages on all servers from the same source for any reason (multiple domains, security policies, etc), but want to write packages so they can be deployed as widely as possible without modification, you probably want to use indirect configuration of the connection manager used for SQL configuration. Then you create an environment variable on each system with the connection string for the configuration table/view it should use, and you're done.

    If you're asking how to retrofit this configuration scheme onto 100+ existing packages, I think there is a lot of manual editing in your future. Migrating packages in the future will probably be a lot easier once they are converted to this scheme.

  • Scott - Great article, I'm finding an issue when I update a SSIS package. After the amendment/additions to the package, I go through the motions using the package configuration organiser. When I look at the table "SSIS_Config_base" I find that the EnvironmentEnum column has reset all the values back to 0.

    Its obviously something that I'm doing wrong!

    Any advice on what I'm doing wrong or how to prevent this would be greatly appreciated.

    Best Regards

    Paul Cox

  • If you implemented the config trigger and history table, it should show you what happened. The only explanation for resetting EnvironmentEnum to 0 it that all the configuration settings were deleted and reinserted. I haven't seen this behavior myself.

    I have tested changing some property values in BIDS and re-saving a configuration, and seen it correctly update only the Development value without affecting the values for other environments. I believe it asks if it should reuse existing configuration, and the answer should be Yes. Maybe you're answering No, which would cause it to delete and re-enter all settings for that ConfigurationFilter?

    I make most value changes using the stored procedure. I would only go through the package configuration organizer if I was adding new properties. I use a lot of small configuration sets to promote reuse (one for each connection manager, email settings, network fileshares, etc) so the effort to recreate one would be small.

    If I had a problem with the designer screwing up the settings, I would probably use the all-environment view to script out SetValue calls with all the existing settings before using the package configuration organizer. After BIDS screws up the settings, run the script to restore the previous values after making any necessary edits to preserve the package changes. (Note that I've added another environment since writing the article.)

    SELECT 'EXEC dbo.SSIS_Config_SetValues @ConfigurationFilter = N''' + ConfigurationFilter + ''', @PackagePath = N''' + PackagePath

    + ''', @AllValues = ' + CASE WHEN GlobalValue IS NULL THEN 'NULL' ELSE 'N''' + GlobalValue + '''' END

    + ', @ProdValue = ' + CASE WHEN ProductionValue IS NULL THEN 'NULL' ELSE 'N''' + ProductionValue + '''' END

    + ', @DevValue = ' + CASE WHEN DevelopmentValue IS NULL THEN 'NULL' ELSE 'N''' + DevelopmentValue + '''' END

    + ', @TestValue = ' + CASE WHEN TestValue IS NULL THEN 'NULL' ELSE 'N''' + TestValue + '''' END

    + ', @StagingValue = ' + CASE WHEN StagingValue IS NULL THEN 'NULL' ELSE 'N''' + StagingValue + '''' END

    FROM dbo.SSIS_Config_AllEnv

    WHERE ConfigurationFilter = N'xyz'

  • Hi Scott

    I just came across this article, and wanted to congratulate you on a very interesting and well thought out approach.



Viewing 15 posts - 16 through 30 (of 57 total)

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