June 9, 2009 at 11:24 am
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! 😉
June 9, 2009 at 12:04 pm
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.
June 9, 2009 at 12:34 pm
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'
SET NOCOUNT ON
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
WHERE
ConfigurationFilter = @filter
SELECT @rc = 1, @PackagePath = MIN(PackagePath)
FROM #Config
WHILE @rc 0
BEGIN
--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
END
DROP TABLE #Config
MCITP, Database Administrator
A hodgepodge of Information Technology and Life
LinkedIn Profile
My Twitter
June 9, 2009 at 3:33 pm
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'
SELECT REPLACE(
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
June 9, 2009 at 3:52 pm
Would you be willing to share your SSIS template? That could really help the push for SSIS in my space.
Thanks
June 10, 2009 at 6:35 am
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
June 10, 2009 at 7:58 am
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?
June 10, 2009 at 8:35 am
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
June 10, 2009 at 8:35 am
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.
June 10, 2009 at 9:00 am
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.
June 22, 2009 at 4:22 am
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
June 22, 2009 at 5:26 am
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.
June 30, 2009 at 8:17 am
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
June 30, 2009 at 9:38 am
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'
July 22, 2009 at 1:25 am
Hi Scott
I just came across this article, and wanted to congratulate you on a very interesting and well thought out approach.
regards,
Joon
Viewing 15 posts - 16 through 30 (of 57 total)
You must be logged in to reply to this topic. Login to reply