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
Nikos Miliotis
Nikos Miliotis
SSC-Enthusiastic
SSC-Enthusiastic (152 reputation)SSC-Enthusiastic (152 reputation)SSC-Enthusiastic (152 reputation)SSC-Enthusiastic (152 reputation)SSC-Enthusiastic (152 reputation)SSC-Enthusiastic (152 reputation)SSC-Enthusiastic (152 reputation)SSC-Enthusiastic (152 reputation)

Group: General Forum Members
Points: 152 Visits: 64
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
Nikos Miliotis
Nikos Miliotis
SSC-Enthusiastic
SSC-Enthusiastic (152 reputation)SSC-Enthusiastic (152 reputation)SSC-Enthusiastic (152 reputation)SSC-Enthusiastic (152 reputation)SSC-Enthusiastic (152 reputation)SSC-Enthusiastic (152 reputation)SSC-Enthusiastic (152 reputation)SSC-Enthusiastic (152 reputation)

Group: General Forum Members
Points: 152 Visits: 64
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?
Scott Coleman
Scott Coleman
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11675 Visits: 1587
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)
http://msdn.microsoft.com/en-us/library/ms345191(SQL.90).aspx
http://support.microsoft.com/kb/908018

Jamie Thomson has a blog entry with a downloadable template package, the next link has an interesting error handling template.
http://blogs.conchango.com/jamiethomson/archive/2007/03/11/SSIS_3A00_-Package-Template.aspx
http://weblogs.sqlteam.com/jamesn/archive/2008/04/30/60582.aspx



Scott Coleman
Scott Coleman
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

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



Scott Coleman
Scott Coleman
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

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



michael.schroeder
michael.schroeder
Valued Member
Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)

Group: General Forum Members
Points: 69 Visits: 65
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?

Michael
Scott Coleman
Scott Coleman
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

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



paul.cox-1109227
paul.cox-1109227
SSC Rookie
SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)

Group: General Forum Members
Points: 35 Visits: 5
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
Scott Coleman
Scott Coleman
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11675 Visits: 1587
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'





jdurandt
jdurandt
SSC Veteran
SSC Veteran (260 reputation)SSC Veteran (260 reputation)SSC Veteran (260 reputation)SSC Veteran (260 reputation)SSC Veteran (260 reputation)SSC Veteran (260 reputation)SSC Veteran (260 reputation)SSC Veteran (260 reputation)

Group: General Forum Members
Points: 260 Visits: 149
Hi Scott

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

regards,
Joon
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