InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comMon, 20 May 2013 10:14:38 GMT20http://www.sqlservercentral.com/Forums/Topic727890-609-1.aspxRE: Multiple environments on the same serverUsing user name instead of (or in addition to) host name is probably the easiest way to handle having multiple environments on the same server.The other connection-specific function is APP_NAME(), but I haven't thought of a simple way to make use of it. The SSIS_Config view could be rewritten to look for the environment setting in APP_NAME(), then you would need a configuration item that sets the SSIS_Config connection manager ApplicationName property before any of the SQL configuration items. Or you could use a parent package that uses a commandline config setting to define a variable, then use parent package configuration to copy that value to ApplicationName. That's too many layers of indirection for me though.RE: Hassles about querying production server from testI had a developer who kept bringing this up. Since the package configuration process takes place before the package starts, does not involve user code, and is read-only, I don't consider it a violation of the separation of environments. Neither of us changed the other's mind, but my opinion was the only one that mattered.But if you're in an rigid environment where the complaints are coming from someone with the authority to enforce them, you have to address it.One idea would be to put the configuration database on its own server, maybe just a simple SQL Express instance. This would have to be a server visible to all your package-execution servers, but if the powers-that-be can accept that it is a configuration server and not part of prod/test/dev then maybe they will go along with it.If you are forced to break the configuration into multiple databases, while still being able to deploy packages with no changes, you could use an environment variable config item to set the ServerName property of the SSIS_Config connection before any SQL configuration items are used.Another way would be to have the network admins define a domain alias that points to the dev config server, and use this name in all your packages. On the package-executing servers that need production config (and also test servers if you need three separate config dbs), you can define an overriding local alias in the hosts file or with a SQL Server Configuration network client alias.To simplify maintenance you could set up a configuration maintenance database that uses a partitioned view to pull the pieces back together and make it look like a single table. An ideal location would be on your laptop or some other server under your control that won't start another multi-environment argument.Getting the partitioned view set up properly requires some changes to all SSIS_Config_base tables.First you need to replace the clustered primary key (ConfigId) of each SSIS_Config_base table with a clustered unique index on ConfigId and a nonclustered primary key on (ConfigId, EnvironmentEnum). Then modify the CK_SSIS_Config_EnvironmentRangeCheck constraint on each base table to limit EnvironmentEnum to the subset used on that server.Now you need a linked server from your config maintenance server to all the separate config servers, and you can define your partitioned view with[code]CREATE VIEW dbo.SSIS_Config_Base ASSELECT * FROM ProdServer.SSIS_Config.dbo.SSIS_Config_baseUNION ALLSELECT * FROM TestServer.SSIS_Config.dbo.SSIS_Config_baseUNION ALLSELECT * FROM DevServer.SSIS_Config.dbo.SSIS_Config_base[/code]The existing SSIS_Config_AllEnv view will work just fine with this base view and pull the separate parts back together. The partitioned view should be updatable because of the primary key and constraint changes listed above, so the SetValues and SplitEnvironments procedures should also work. The MergeEnvironments procedure is a problem because global settings (EnvironmentEnum=0) will only exist in one environment. To fix this and also handle any ad-hoc queries, you could define an INSTEAD OF trigger on the partitioned view to make sure global value changes are replicated to the other dbs. This would handle changes made in the maintenance database, but there's no good way to handle new global configurations created in BIDS. They will be created on the dev config server, and it won't be able to write them to prod. They will have to be fixed before the package can be deployed.The last step would be to modify the Config_Inconsistent view so it considers global + production settings to be required rather than an exception. All procedures and views (except SSIS_Config) should be removed from the config databases and exist only in the maintenance database.Feel free to take this list of all the hoops you will have to jump through with you when you reopen the discussion of whether the SSIS configuration scheme should be exempted from cross-environment prohibitions. Play up the notion of needing a domain alias and access to production hosts files for extra leverage.Tue, 18 Sep 2012 20:40:10 GMTScott Colemanhttp://www.sqlservercentral.com/Forums/Topic727890-609-1.aspxHi KimI implemented this in an environment with similar constraints. I had QA and PROD on the same server, but was logging in with different user names, so I made the view return values from different environments based on the logged in user,Regards,JoonTue, 24 Jul 2012 12:51:35 GMTjdurandthttp://www.sqlservercentral.com/Forums/Topic727890-609-1.aspxI know this is an old post, but I have a question about this approach. I have used this in multiple servers in the past with no issues, but my current customer is using the same physical server for dev and qa. This means I get the same HOST_NAME() when running SSIS packages in both environments.Is these any way to make this work that will allow me to deploy packages and run via SQL agent jobs on both environments? Is there any way for the SSIS package to distinguish between them and return the proper rows in the view?Thanks,KimTue, 24 Jul 2012 12:14:45 GMTktalley.dbahttp://www.sqlservercentral.com/Forums/Topic727890-609-1.aspxHello Scott,Very useful article.I've some different scenerio. I've one server where all the SSIS package jobs runs. I would like to have only one SSIS package to run in all the different prod servers. Luckly, this SSIS package is using same database in all the environments. Your help is appreciated.Sat, 08 Oct 2011 15:25:49 GMTVicky1http://www.sqlservercentral.com/Forums/Topic727890-609-1.aspxI second the earlier question... How would you apply this to environments with firewalls, or in my case security restrictions, between application tiers? I currently have this set up in my company with Dev, Stage, and Prod all hitting the same Production SSIS_Configurations database, and it is working, but I get constant push-back from engineering on why Stage is hitting a Production table. We used to have two separate SSIS_Configurations databases, one for Prod and one from Dev/Stage, but it was high-maintenance, and to me that seemed to defeat the purpose, which is to manage everything from one table. Has anyone else had this problem?Thu, 15 Sep 2011 17:05:53 GMTKDBAhttp://www.sqlservercentral.com/Forums/Topic727890-609-1.aspxNo worries on the delay, Scott. I understand how it can be with the deluge of e-mails.The behavior that you mentioned, not having the ability to use a command-line switch, has changed in SQL 2008 right? You can use the /connection switch to set the connection from which you would obtain configuration values. (http://msdn.microsoft.com/en-us/library/ms141682.aspx)I work in an environment where we cannot use environment variables and using XML files is not an option either (obtaining the permissions on the network shares for the SQL Server accounts would take FOREVER, if possible!), so we use SQL Server configuration, but with the /connection switch. It's not as robust as your solution, but it works. And I will keep your solution in mind for different environments.Thanks...ChrisTue, 30 Aug 2011 06:40:45 GMTCGSJohnsonhttp://www.sqlservercentral.com/Forums/Topic727890-609-1.aspx(Sorry for the late response, I've been ignoring my generic email because it's full of junk.)You can configure the connection for SQL configuration several ways, including environment variables. What's important to keep in mind are the ways you can't configure them.You can't configure the configuration connection with a command line switch, as this switch will not be processed until after all the configuration settings have been read. Ditto for parent package configurations.Any other direct configuration technique (environment variable, XML file, a separate SQL configuration) can modify the connection manager to control the source for later SQL configurations.Expressions on the SQL config connection manager will not be evaluated until after the configuration settings are read, so the connection manager properties must be configured directly and not through a package variable.Sat, 20 Aug 2011 11:10:45 GMTScott Colemanhttp://www.sqlservercentral.com/Forums/Topic727890-609-1.aspxHi, Scott. Thanks for the great article. To clarify a point that you made in the comments, to configure the configuration connection string, you have to use an environment variable? Otherwise, simply use a dedicated configuration connection? This is always the case with SSIS and SQL Server configuration?Thanks...ChrisMon, 04 Apr 2011 13:19:27 GMTCGSJohnsonhttp://www.sqlservercentral.com/Forums/Topic727890-609-1.aspxThe dev and test servers I use have copies of the production databases on separate servers, so when configuring connections the server name is different for each environment but the database name (initial catalog) is the same in every environment.If you have multiple dev, test, and prod servers for different application domains you can define more than one server as dev, test, or prod. My setup has evolved to use a table to define the roles for a large number of servers.[code]CREATE VIEW [dbo].[SSIS_Config] ASSELECT ConfigurationFilter, PackagePath, ConfiguredValueType, ConfiguredValueFROM dbo.SSIS_Config_base bJOIN ( SELECT CurrentEnvironment = ISNULL( ( SELECT Environment FROM ServerEnvironment WHERE ServerName = HOST_NAME() ), 2 )) eON b.EnvironmentEnum = 0 OR b.EnvironmentEnum = e.CurrentEnvironment[/code]If you need more separation to isolate different groups of SSIS packages you could put the configuration for each domain in different servers and/or databases.Tue, 21 Dec 2010 06:46:17 GMTScott Colemanhttp://www.sqlservercentral.com/Forums/Topic727890-609-1.aspxHi Scott, We ran into an interesting issue. We are running our jobs on application database servers arapt from regular database servers( prod,dev,test). So the concept of hostname() is not working because it is pointing to only Application production servers which is purely dedicated to run SQL agent jobs. I don't have clue to proceed further. Please help me out with this.Thanks,SivaMon, 20 Dec 2010 14:00:04 GMTsivaprasadyellalahttp://www.sqlservercentral.com/Forums/Topic727890-609-1.aspxHi Scott, If Properties that are the same in all environments have one record with a value of 0, then how come we acess them when environment changes bcause production is say 1, development is say 2 and so on. Since the properties are environment dependent how come we point the single property values in different servers? I am really struck at this point. Please clarify my pointThanks,SivaMon, 20 Dec 2010 13:42:11 GMTsivaprasadyellalahttp://www.sqlservercentral.com/Forums/Topic727890-609-1.aspx[quote][b]adrinkwine (11/11/2009)[/b]It appears the SQL Job isn't passing in my string value correctly. Its a basic UNC path to a folder, but the path does have some spaces it a couple of the directory names and I think this is where its blowing up.[/quote]Here is what I use to pass in a string variable that may contain spaces etc. The \" is for double quote with the backslash being the escape character. In the event your string variable value will be empty you must pass in two single quotes instead.[code="other"]/SET "\"\Package.Variables[MyVariable].Value\";\"\\servername\foldername\filename.txt\""[/code]One little caveat to doing this, Sql Agent doesn't treat this very nicely, it will mess it up when you save the file so you have to manually change it using the procs for the job. I create jobs through the .NET interface so its not an issue, but if you're using the SQL Agent beware it's going to fubar it.Wed, 23 Dec 2009 08:35:51 GMTBradley Deemhttp://www.sqlservercentral.com/Forums/Topic727890-609-1.aspx[quote][b]jdurandt (7/28/2009)[/b][hr]Earlier in this comment list it is suggested that the program_name() function be used to separate the different environments. I do not think that this is possible, as the connection used for reading SQL Server configurations is not configurable, i.e. you cannot change the "Application Name" in the scheduled job for that connection.[/quote]You can configure the connection used for SQL configuration, but not with a command-line configuration. Command-line configs are applied after the other package configs. The SQL Agent SSIS Task builder is just a fancy interface for creating DTEXEC commands, with any changes you make added as command-line parameters.You can use indirect configuration to get the SQL Config connection string from an environment variable, for instance, assuming that config item appears earlier in the list than the SQL config items. Then you could take the DTEXEC command from your existing SSIS job step and put it in a bat file after some SET commands for the environment variables.Not that I'm recommending this approach, I just had to mention it as a possibility. Every additional level of indirection makes it more difficult to manage and debug.Wed, 11 Nov 2009 13:22:24 GMTScott Colemanhttp://www.sqlservercentral.com/Forums/Topic727890-609-1.aspxI think you may need to escape the quotes too... I vaguely recall running into something like this myself a couple of years back... Something to do with the quotes being stripped out at runtime?? I think it depends on how you call the package from the SQL agent. i.e. If you call DTEXEC from the cmd option or do you use the Integration Services option.HTHWed, 11 Nov 2009 12:32:01 GMTFrank Bazanhttp://www.sqlservercentral.com/Forums/Topic727890-609-1.aspxOkay, so I've implimented this and I like it, a lot! I am having an issue that I'm hoping anybody can help me resolve. I am a consultant and the client Im at doesn't have a standard UAT Integration Services environment (dont ask ;). As a result, we deploy the packages to the Dev environment with Package Config enabled, but I override a couple values using the Data Values tab in the SQL Job. It works great for the database connections (data source tab) and for 2 INT variables, but one of the variables that needs to be set for the UAT environment is a folder directory where some files are located. It appears the SQL Job isn't passing in my string value correctly. Its a basic UNC path to a folder, but the path does have some spaces it a couple of the directory names and I think this is where its blowing up. In the SSIS pkg, I have a script task that uses this path to do stuff with the files but I keep getting errors with everything I've tried thus far. The simplest solution is to quote this entire string in the SQL Job, the error I get is "Invalid characters in path". The errors I get are from inside the package and not from the job. Furthermore, I can execute using dtexec by just quoting my string and using dbl back slashes and that works, so this says to me the SQL Job isnt passing the string into the package properly.This is only a problem when I try overriding these values through the SQL Job. Using the Config normally or in Debug mode everything works fine with the string value I'm trying to use.Examples of what I've tried:"\\server\dir1\dir 2\" - error "invalid characters in path"\\\\server\\dir1\\dir 2\\ (with and w/o quotes around the whole string). This error shows me the correct path, but says it cant find the file.\/\/server\/dir1\/dir 2\/ (with and w/o quotes around the whole string). My VB script task doesnt error but doesnt do anything. The next step in the package (to open the file) gives the error: No destination flat file name was provided.//server/dir1/dir 2/ generated an error in the next step in the package. Doesnt generate an error, but the package doesnt do what its suppose to.\\server\dir1\dir%202\ (with and w/o quotes around the whole string). Illegal characters in path.Any feedback is greatly appreaciated!Wed, 11 Nov 2009 12:08:03 GMTadrinkwinehttp://www.sqlservercentral.com/Forums/Topic727890-609-1.aspxI have now implemented a configuration scheme like this, and thought that I would share my learnings.I had to add "Instead Of" triggers to my view to manage the inserts, updates and deletes of config items. Without this, the records were inserted with null environments, and the udpates got errors when setting configuration values.I also have a case where two environments share a server. Earlier in this comment list it is suggested that the program_name() function be used to separate the different environments. I do not think that this is possible, as the connection used for reading SQL Server configurations is not configurable, i.e. you cannot change the "Application Name" in the scheduled job for that connection. We do have different user accounts for the separate environments, so we ended up using the user name, so scheduling the package as the QA user picks up the QA settings, and as the UAT user picks up the UAT settings.Tue, 28 Jul 2009 00:22:38 GMTjdurandthttp://www.sqlservercentral.com/Forums/Topic727890-609-1.aspxI have now implemented a configuration scheme like this, and thought that I would share my learnings.I had to add "Instead Of" triggers to my view to manage the inserts, updates and deletes of config items. Without this, the records were inserted with null environments, and the udpates got errors when setting configuration values.I also have a case where two environments share a server. Earlier in this comment list it is suggested that the program_name() function be used to separate the different environments. I do not think that this is possible, as the connection used for reading SQL Server configurations is not configurable, i.e. you cannot change the "Application Name" in the scheduled job for that connection. We do have different user accounts for the separate environments, so we ended up using the user name, so scheduling the package as the QA user picks up the QA settings, and as the UAT user picks up the UAT settings.Tue, 28 Jul 2009 00:14:05 GMTjdurandthttp://www.sqlservercentral.com/Forums/Topic727890-609-1.aspxHi ScottI just came across this article, and wanted to congratulate you on a very interesting and well thought out approach.regards,JoonWed, 22 Jul 2009 01:25:20 GMTjdurandthttp://www.sqlservercentral.com/Forums/Topic727890-609-1.aspxIf 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.)[code]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 + '''' ENDFROM dbo.SSIS_Config_AllEnvWHERE ConfigurationFilter = N'xyz'[/code]Tue, 30 Jun 2009 09:38:18 GMTScott Colemanhttp://www.sqlservercentral.com/Forums/Topic727890-609-1.aspxScott - 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 CoxTue, 30 Jun 2009 08:17:03 GMTpaul.cox-1109227http://www.sqlservercentral.com/Forums/Topic727890-609-1.aspxIf 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.Mon, 22 Jun 2009 05:26:44 GMTScott Colemanhttp://www.sqlservercentral.com/Forums/Topic727890-609-1.aspxHi 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?MichaelMon, 22 Jun 2009 04:22:27 GMTmichael.schroederhttp://www.sqlservercentral.com/Forums/Topic727890-609-1.aspx[quote][b]Nikos Miliotis (6/10/2009)[/b][hr]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?[/quote]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.Wed, 10 Jun 2009 09:00:12 GMTScott Colemanhttp://www.sqlservercentral.com/Forums/Topic727890-609-1.aspx[quote][b]Nikos Miliotis (6/9/2009)[/b][hr]In which database, do i have to store the SSIS_Config table?[/quote]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.Wed, 10 Jun 2009 08:35:42 GMTScott Colemanhttp://www.sqlservercentral.com/Forums/Topic727890-609-1.aspx[quote][b]whug (6/9/2009)[/b][hr]Would you be willing to share your SSIS template?[/quote]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)[url]http://msdn.microsoft.com/en-us/library/ms345191(SQL.90).aspx[/url][url]http://support.microsoft.com/kb/908018[/url]Jamie Thomson has a blog entry with a downloadable template package, the next link has an interesting error handling template.[url]http://blogs.conchango.com/jamiethomson/archive/2007/03/11/SSIS_3A00_-Package-Template.aspx[/url][url]http://weblogs.sqlteam.com/jamesn/archive/2008/04/30/60582.aspx[/url]Wed, 10 Jun 2009 08:35:06 GMTScott Colemanhttp://www.sqlservercentral.com/Forums/Topic727890-609-1.aspxAs 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?Wed, 10 Jun 2009 07:58:52 GMTNikos Miliotishttp://www.sqlservercentral.com/Forums/Topic727890-609-1.aspxOne 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 MiliotisWed, 10 Jun 2009 06:35:02 GMTNikos Miliotishttp://www.sqlservercentral.com/Forums/Topic727890-609-1.aspxWould you be willing to share your SSIS template? That could really help the push for SSIS in my space.ThanksTue, 09 Jun 2009 15:52:50 GMTWHughttp://www.sqlservercentral.com/Forums/Topic727890-609-1.aspxThat's an interesting idea. I think I would have it script out the command with the current values:[code]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_AllEnvWHERE ConfigurationFilter = @filter[/code]Tue, 09 Jun 2009 15:33:38 GMTScott Colemanhttp://www.sqlservercentral.com/Forums/Topic727890-609-1.aspxI 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.[code]--Generate SQL for setting the values--EXEC dbo.SSIS_Config_SetValues 'Filter',N'Path',N'New value' SET NOCOUNT ONDECLARE @sql varchar(1000), @filter varchar(1000), @rc int, @PackagePath varchar(1000)--insert your filter hereSET @filter = 'InsertYourConfigurationFilterHere'SELECT ConfigurationFilter, PackagePathINTO #ConfigFROM dbo.SSIS_Config_baseWHEREConfigurationFilter = @filterSELECT @rc = 1, @PackagePath = MIN(PackagePath)FROM #ConfigWHILE @rc <> 0BEGIN--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 = PackagePathFROM #ConfigWHERE PackagePath > @PackagePathORDER BY PackagePathSET @rc = @@ROWCOUNTENDDROP TABLE #Config[/code]Tue, 09 Jun 2009 12:34:12 GMTJason Criderhttp://www.sqlservercentral.com/Forums/Topic727890-609-1.aspx[quote][b]sqlJunkies2 (6/9/2009)[/b][hr]they will inadvertently reset all the production value to development value settings from the package[/quote]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.Tue, 09 Jun 2009 12:04:03 GMTScott Colemanhttp://www.sqlservercentral.com/Forums/Topic727890-609-1.aspxHi 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! ;-)Tue, 09 Jun 2009 11:24:34 GMTsqlJunkies2http://www.sqlservercentral.com/Forums/Topic727890-609-1.aspxGreat article. I think Microsoft should built in something like this into the next release of SQL Server (and pay royalties to Scott ;-))Mon, 08 Jun 2009 17:59:16 GMTmishalubahttp://www.sqlservercentral.com/Forums/Topic727890-609-1.aspx[quote][b]John Rowan (6/3/2009)[/b][hr]Just curious, what are you using for your Package Protection Level, server storage?[/quote]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.Wed, 03 Jun 2009 11:55:47 GMTScott Colemanhttp://www.sqlservercentral.com/Forums/Topic727890-609-1.aspxGreat article Scott. I love your approach. Just curious, what are you using for your Package Protection Level, server storage?Wed, 03 Jun 2009 09:24:37 GMTJohn Rowanhttp://www.sqlservercentral.com/Forums/Topic727890-609-1.aspxWell 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.DavidWed, 03 Jun 2009 09:11:22 GMTDavidSimpsonhttp://www.sqlservercentral.com/Forums/Topic727890-609-1.aspxI 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.[url]http://www.sqlservercentral.com/Forums/Topic724499-148-1.aspx#bm724516[/url]Wed, 03 Jun 2009 08:38:17 GMTBradley Deemhttp://www.sqlservercentral.com/Forums/Topic727890-609-1.aspxVery good article and a very comprehensive approach, Scott. Was using same tech for single server but had not for multi-environment. Would definitely try out in near future.Again, thanks for the sql configuration.sqlWed, 03 Jun 2009 07:44:40 GMTSanjayAttrayhttp://www.sqlservercentral.com/Forums/Topic727890-609-1.aspx[quote][b]Simon Storey (6/3/2009)[/b][hr]Any chance you can post sample of your technique to swap connections based on environment dynamically.[/quote]The article describes a way of managing multiple package configurations easily, it assumes the reader already has some knowledge of configuration usage. The example package in the article only modifies some package variable values, but it works the same way for connection manager properties, task properties, or anything else you want to configure.1. Create a package with a connection manager.2. Use package configuration to put the connection manager properties in a SQL table.3. Use the techniques in the article to supply multiple values.The SSISConfiguration.sql file linked under "Resources" in the article has all the code.Wed, 03 Jun 2009 07:10:22 GMTScott Colemanhttp://www.sqlservercentral.com/Forums/Topic727890-609-1.aspxReally good article. I'm going to adapt my current configuration table to use a trigger similar to yours since I hadn't thought of that.Wed, 03 Jun 2009 06:59:42 GMTJason Crider