Click here to monitor SSC
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
Bradley Deem
Bradley Deem
Mr or Mrs. 500
Mr or Mrs. 500 (567 reputation)Mr or Mrs. 500 (567 reputation)Mr or Mrs. 500 (567 reputation)Mr or Mrs. 500 (567 reputation)Mr or Mrs. 500 (567 reputation)Mr or Mrs. 500 (567 reputation)Mr or Mrs. 500 (567 reputation)Mr or Mrs. 500 (567 reputation)

Group: General Forum Members
Points: 567 Visits: 1248
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
DavidSimpson
SSC Eights!
SSC Eights! (976 reputation)SSC Eights! (976 reputation)SSC Eights! (976 reputation)SSC Eights! (976 reputation)SSC Eights! (976 reputation)SSC Eights! (976 reputation)SSC Eights! (976 reputation)SSC Eights! (976 reputation)

Group: General Forum Members
Points: 976 Visits: 1074
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
John Rowan
SSCarpal Tunnel
SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)

Group: General Forum Members
Points: 4072 Visits: 4510
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 - by Jeff Moden
Scott Coleman
Scott Coleman
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2942 Visits: 1415
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
Misha_SQL
SSChasing Mays
SSChasing Mays (607 reputation)SSChasing Mays (607 reputation)SSChasing Mays (607 reputation)SSChasing Mays (607 reputation)SSChasing Mays (607 reputation)SSChasing Mays (607 reputation)SSChasing Mays (607 reputation)SSChasing Mays (607 reputation)

Group: General Forum Members
Points: 607 Visits: 983
Great article. I think Microsoft should built in something like this into the next release of SQL Server (and pay royalties to Scott ;-))



sqlJunkies2
sqlJunkies2
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
Points: 15 Visits: 41
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! ;-)
Scott Coleman
Scott Coleman
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

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



Jason Crider
Jason Crider
SSC Veteran
SSC Veteran (285 reputation)SSC Veteran (285 reputation)SSC Veteran (285 reputation)SSC Veteran (285 reputation)SSC Veteran (285 reputation)SSC Veteran (285 reputation)SSC Veteran (285 reputation)SSC Veteran (285 reputation)

Group: General Forum Members
Points: 285 Visits: 2229
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
Scott Coleman
Scott Coleman
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2942 Visits: 1415
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





WHug
WHug
Valued Member
Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)

Group: General Forum Members
Points: 53 Visits: 394
Would you be willing to share your SSIS template? That could really help the push for SSIS in my space.

Thanks



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