March 12, 2014 at 5:03 am
Hello,
For a particular SSIS 2008 R2 ETL solution, we distinguish four environments:
- Development
- Test
- User Acceptance Test
- Production
We use the configuration approach described in 'Defining a Configuration Approach for Integration Services Packages'
(http://msdn.microsoft.com/en-us/library/cc671625.aspx), in particular the SQL Server Approach: At the server level an environment variable is defined which
contains the connection string to the SQL Server configuration database. Every package configuration starts with the setting of the connectionstring of the
connection manager of the configuration database using the environment variable followed by the necessary SQL Server Table configurations.
Now this approach works fine, except for the User Acceptance Test- and Production-environment
Point is that these environments reside on the same Windows server, i.e. share the same environment variable. Both environments have their own databasebase
server with an own configuration database. Packages reside on the file system and both environments have their own package location and SQL Server Agent Job
which kicks off the main package with the dtexec utility. I quess you can now anticipate my problem/question: At startup (i.e. configuration phase) the main
package references the environment variable which contains of course the connection string of just 1 configuration database (the one for User Acceptance Test
or Production): the main package must decide which configuration database to use and the only clue it has is it's own execution location. I can think of
some workarounds and in practice our DBA has defined two environment variables (one for each environment/configuration database) and after each deployment of
a new release manually update the XML of the packages (i.e. search/replace the name of the environment variable), not very nice.
I'm a little bit puzzled because this seems to me a pretty common scenario (i.e. two ETL environments on the same machine) but I cannot find a clear solution
for the configuration of these environment.
Any suggestions?
Thanks very much,
Gert Broekhaar
RIVM, The Netherlands
March 12, 2014 at 5:59 am
Moving to SSIS 2012 is a good solution, if your scope extends that far. You'd be able to make good use of 'environments' within the new SSISDB 'Integration Services Catalog' and throw away all your external (out-of-SQL Server) config.
March 12, 2014 at 9:03 pm
Go with Phil's suggestion if you can.
Assuming that you are using separate SQL Server instances, another option is to use "User" environment variables instead of "System" environment variables. Use separate service accounts for each instance (including SQL Agent). Each instance will get its own set of "User" environment variables and, hence, you can have different values stored for each instance.
From SSIS, you cannot distinguish - you are simply specifying the name of the environment variable. At runtime, the package reads the environment variable value for the account it runs as (which, for most of us ends up being the service account that SQL Agent uses although you could use a credential to change that)
As an added "bonus", you are also able to have better separation of each instance on the Windows Server by ensuring that the relevant services accounts do not have any privileges in the other instance.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply