Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««12345»»»

SSIS multi-environment configuration in a single SQL Server table Expand / Collapse
Author
Message
Posted Wednesday, June 10, 2009 6:35 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, May 12, 2014 6:07 AM
Points: 110, 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

Post #732181
Posted Wednesday, June 10, 2009 7:58 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, May 12, 2014 6:07 AM
Points: 110, 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?
Post #732306
Posted Wednesday, June 10, 2009 8:35 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, July 18, 2014 12:13 PM
Points: 2,837, Visits: 1,138
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



Post #732346
Posted Wednesday, June 10, 2009 8:35 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, July 18, 2014 12:13 PM
Points: 2,837, Visits: 1,138
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.



Post #732348
Posted Wednesday, June 10, 2009 9:00 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, July 18, 2014 12:13 PM
Points: 2,837, Visits: 1,138
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??

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.



Post #732364
Posted Monday, June 22, 2009 4:22 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, September 14, 2011 8:04 AM
Points: 35, Visits: 57
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
Post #739259
Posted Monday, June 22, 2009 5:26 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, July 18, 2014 12:13 PM
Points: 2,837, Visits: 1,138
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.



Post #739296
Posted Tuesday, June 30, 2009 8:17 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, October 14, 2009 4:30 AM
Points: 1, 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
Post #744442
Posted Tuesday, June 30, 2009 9:38 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, July 18, 2014 12:13 PM
Points: 2,837, Visits: 1,138
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'




Post #744546
Posted Wednesday, July 22, 2009 1:25 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 12:40 PM
Points: 54, Visits: 145
Hi Scott

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

regards,
Joon
Post #757189
« Prev Topic | Next Topic »

Add to briefcase «««12345»»»

Permissions Expand / Collapse