July 24, 2012 at 12:51 pm
Hi Kim
I 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,
Joon
September 18, 2012 at 8:40 pm
RE: Multiple environments on the same server
Using 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 test
I 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
CREATE VIEW dbo.SSIS_Config_Base AS
SELECT * FROM ProdServer.SSIS_Config.dbo.SSIS_Config_base
UNION ALL
SELECT * FROM TestServer.SSIS_Config.dbo.SSIS_Config_base
UNION ALL
SELECT * FROM DevServer.SSIS_Config.dbo.SSIS_Config_base
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.
July 5, 2013 at 1:55 pm
Can you provide the code for the stored procedure you are using SSIS_Confit_SplitEnvironments?
July 8, 2013 at 6:52 am
There is a link at the end of the article under "Resources" to a .sql file with all the views and procedures.
August 20, 2015 at 10:32 pm
Hi Scott,
Thanks for the article. I realize it is five years old but I've been messing around with your solution and REALLY like it. You see I'm super new to SSIS. I have a coding background so I wanted to tackle the environment and logging issues first to minimize maintenance.
I'm working with SQL 2014. From what I have found, I believe your solution is the most elegant and easiest to maintain but I have to ask if there is something new in 2014 that I should be using in addition to or instead of your approach?
I'm still searching around and reading a bunch of stuff but there is so much out there. It is overwhelming at times. Do you have any advice or suggestions for newbie? Even some suggested Google search terms would be appreciated.
Hoping you're still replying to questions,
Aaron
August 25, 2015 at 12:46 pm
I've looked at the new package configuration and deployment features in SQL 2012-2014, and while I wish they were there from the beginning I don't see anything that makes me want to give up on my SQL configuration. Granted some of my reservations may be due to limited use of the new project features and therefore not being completely familiar with them. I believe you can mix SQL configuration with project configuration, so you might do both. With project configuration you can mark things like passwords sensitive and they will be encrypted and hidden, although with some effort anyone with access to the project source code can get the plain text values.
Using project properties for configuration is great, but only applies to a single project. If you have multiple teams developing their own SSIS packages in their own projects, and if a configuration change needs to be made across all projects (say the name of the corporate mail server changes) then all those teams have to open all their projects, modify the property, and redeploy. If a package is deployed to multiple servers, then multiple redeployments. I just have to make a change to my config table in SQL and everyone's packages continue to work.
I also like that in my system the choice of production vs test vs dev happens automatically. The new project configuration scheme lets you define environment-based configuration values and deploy a specific configuration at will, which is a great improvement over juggling multiple XML config files. But I worry that someone will at one point deploy the wrong version somewhere through inattention or negligence, and how would you check on that afterwards?
One of these days we will finish our Windows Server 2003 and SQL 2005 migrations (lots of technical debt around here), I can finally install and play with SQL 2014, and maybe get seduced by the new configuration features. But so far I haven't seen any reason to.
November 16, 2015 at 4:48 am
I like this solution and I am using it.
However the solution has a potential problem one needs to be aware of.
When you edit an existing SQL Server-based configuration from within the Package Configuration Wizard it reinserts all configurations with the EnvironmentEnum=0
So basically once the configurations, initially created using the Package Configuration Wizard (or manually by directly inserting into the table), for subsequent edits (including editing existing configurations or adding new ones) the Package Configuration Wizard must not be used.
November 16, 2015 at 4:52 am
Also I have added an Environments table and changed the [SSIS_Config]
CREATE TABLE [dbo].[SSIS_Environments](
[EnvironmentId] [tinyint] NOT NULL,
[Environment] [nvarchar](10) NOT NULL,
[EnvironmentDesc] [varchar](11) NULL,
[HostName] [nvarchar](20) NULL,
[InstanceName] [nvarchar](20) NULL,
[ServerName] AS ([HostName]+coalesce('\'+[InstanceName],'')),
CONSTRAINT [PK_Environments] PRIMARY KEY CLUSTERED
(
[EnvironmentId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE VIEW [dbo].[SSIS_Config] AS
SELECTConfigurationFilter,
PackagePath,
ConfiguredValueType,
ConfiguredValue
FROM dbo.SSIS_Config_base b
WHERE 1=1
AND (
b.EnvironmentEnum = 0
OR
b.EnvironmentEnum = (COALESCE((SELECT [EnvironmentID] FROM [dbo].[SSIS_Environments] WHERE [ServerName] LIKE HOST_NAME() + '%' ),2))
)
GO
November 17, 2015 at 2:44 pm
The Configuration Wizard has no knowledge of the extra columns in the base table, and the SSIS_Config view it is updating doesn't include the EnvironmentEnum column so anything it inserts will have to default to EnvironmentEnum=0. In some cases it will do an update instead of an insert and only change the value for one environment, but that will be based on where BIDS is running and not the environment you had in mind when you made the change. But it really isn't safe to let the Configuration Wizard stomp on existing configuration sets.
You can use the Configuration Wizard to add new configuration items with a dummy ConfigurationFilter value, then go to the SSIS_Config database and see the results. Then you can split them into multi-environment values if desired, and move them into an existing set by updating the ConfigurationFilter column. Then go back to the package Configuration Wizard to remove the dummy config set.
November 7, 2017 at 9:08 am
Hello All!
Could someone please make a tutorial from beginning to the end on how this works? I was trying to get this to work but no luck. Its also nice if we could have the Integration Services Solution of this Sample Package.
Any help is much appreciated!
Thanks in advance,
mark
November 9, 2017 at 2:44 pm
This is an approach that worked well with SQL 2005 and 2008, maybe through 2012. But with SQL 2016 you're better off using package variables that are set by project variables, which in turn are set by environment variable collections. Then you can deploy one copy of the package and run it anywhere, using command-line options to select the environment. If running a package from SQL Agent, look at the advanced settings on the job step to select environments.
But to summarize the original approach:
Creating a SQL Server configuration set in a package will create a table in the database you've selected. Each property is unique by ConfigurationFilter and PackagePath. My approach added some additional columns that are ignored by SSIS, and added the EnvironmentEnum tinyint to the primary key so properties could be multi-valued. This table is named SSIS_Config_Base and is not referenced directly by any packages.
I added a ServerEnvironment table which lists servers that run can run packages (at least the non-development servers), and the environment that should be used for each one. So a given server is considered to be Production (or Test or Development) for all packages running on it. Anything not listed, say your laptop running BIDS, is considered Development.
When a package starts, it will query the SSIS_Config view for configuration properties. This uses HOST_NAME() to determine where the package is running, and selects the rows based on the ConfigurationFilter from the package and the EnvironmentEnum from ServerEnvironment. So the environment-specific values are returned to the package.
November 10, 2017 at 3:35 am
Scott Coleman - Thursday, November 9, 2017 2:44 PMThis is an approach that worked well with SQL 2005 and 2008, maybe through 2012. But with SQL 2016 you're better off using package variables that are set by project variables, which in turn are set by environment variable collections. Then you can deploy one copy of the package and run it anywhere, using command-line options to select the environment. If running a package from SQL Agent, look at the advanced settings on the job step to select environments.But to summarize the original approach:
Creating a SQL Server configuration set in a package will create a table in the database you've selected. Each property is unique by ConfigurationFilter and PackagePath. My approach added some additional columns that are ignored by SSIS, and added the EnvironmentEnum tinyint to the primary key so properties could be multi-valued. This table is named SSIS_Config_Base and is not referenced directly by any packages.I added a ServerEnvironment table which lists servers that run can run packages (at least the non-development servers), and the environment that should be used for each one. So a given server is considered to be Production (or Test or Development) for all packages running on it. Anything not listed, say your laptop running BIDS, is considered Development.
When a package starts, it will query the SSIS_Config view for configuration properties. This uses HOST_NAME() to determine where the package is running, and selects the rows based on the ConfigurationFilter from the package and the EnvironmentEnum from ServerEnvironment. So the environment-specific values are returned to the package.
Hi Scott!
Thanks for your reply! I do undertood the "Theory" behind it. Im Using Visual Studio 2013 for BIDS and SSMS SQL Server 2014 but i cant just get this working. Could you maybe send me the DemoPackage from this Example? That would be reall great!
Thanks in advanced!
Mark
November 13, 2017 at 9:20 am
The SSISConfiguration.sql attachment on the original article should be all you need. You don't say what's not working, but here are some suggestions.
The SSIS_Config_AllEnv view should show you what values you have configured for each environment.
To set up a non-development environment for a specific server, add the server name and environment number to the Server Environment table. Make sure you use the @@SERVERNAME value for that server, I have a number of servers commonly referred to by domain alias but the value returned by HOST_NAME() will match @@SERVERNAME and not be the name used for the connection.
Go to the server where you plan to run your package and create a linked server (if necessar) to wherever your configuration database is located. When you query "SELECT * FROM .[config db].dbo.SSIS_Config", you should get the configuration values appropriate for that environment. If you query from a server in a different environment, you should get different values for multi-valued items.
If your package has a connection manager pointing to the configuration database, and the package loads configuration sets using this connection and the SSIS_Config view, it should get the correct values. Assuming the PackagePath entries put the configured values in the right place in your package.
Copy the demo package in the original article. Create a package with a variable or two, a connection to the SSIS_Config database, and create a configuration set from the variable values. Expand these into multi-valued parameters in the configuration database. Add a script task to popup a message box with the variable values. Using Remote Desktop, open BIDS on servers in different environments (according to the ServerEnvironment table) and run this package.
Viewing 13 posts - 46 through 57 (of 57 total)
You must be logged in to reply to this topic. Login to reply