August 19, 2014 at 4:04 pm
We are in currently in the process of conversion to SSIS 2012 from 2008 R2.
In our current setup we use a Windows environment variable that is on every server and every development computer. This variable is specified as the first config in every package and contains the connection string that tells the rest of the configs where to get their values.
We use SQL Config tables, and each project normally references 2 config tables, one for connections and one for the specific project configs. So we have 1 big connections config table and a bunch of smaller tables, one for each project.
The connections config table is used in every package and contains all the different connection strings for DB connections, FTP, File Connections, Look up Cache connections, etc.....
This config tables are duplicated across all our prod SSIS servers there is also versions for QA that contains all the QA connection strings and values and a set for development.
Every connection we use across all environments is named the same. This allows us to run a package on any server we choose and it always points to the correct resources whether it be prod, test, or dev. Furthermore when something is checked out to be worked on it doesn't matter what the connections were originally set to, when opened in BIDS they always point to the Dev resources, of course when the package is saved it retains those values.
another benefit is if the connections already exist we just add connection managers to the project using the exact name then then pick them from the config list, select re-use existing and done. From that point on there is no need to touch them again including deployments to QA or production.
So now I need to make use of the 2012 environments feature to duplicate this.
First I've imported all the connections into the environment using the catalog procedure for creating new variables. I also see that I can script out the parameter values to be setup to reference the Environment values so I don't need to go into each one and manually set the parameters for every project.
In the old setup naming had to be exact since it was the package path value that was used to link the config. Now that is gone and it seems the replacement to that is to have all parameter names and environment variable names be the same, this way we can setup scripts to link them as mentioned above.
when opening the package in BIDS how do I specify that the connections will be pointing to a dev environment?
the benefits of Environments are eluding me at the moment...
Looking for some discussion on this.
August 19, 2014 at 11:50 pm
Some quick questions and thoughts:
Are you migrating to the project deployment model?
Do your projects make full use of shared parameters and connection managers?
Have you changed the variables you want to control via environments to parameters?
The default values for your Connection Managers and Project Params should be the dev values. When you open them in SSDT (it's not called BIDS any more), all will be well, because environments are not being used there.
After you have deployed a project, you can create one or more 'References' to it, via the Configure option. Multiple environments can contain the same variable, but with different values. If that environment variable is 'mapped' to a parameter, you can select which environment to 'use' when you execute the package. So you can execute the same package with different values for connections and parameters, just by specifying which environment you want to use when you execute it.
Repeated deployments of the same project retain the environment settings - so while the initial config takes some time, it's mostly a one-off piece of work - plus ongoing maintenance & tweaks, of course.
If the answer to my first three questions was 'yes', you should find environments a powerful and, when you've got over the initial learning curve, relatively simple to understand, configuration tool.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
August 20, 2014 at 2:13 pm
Thanks for the response Phil.
I am starting to see some benefits and after we get rolling I'm sure we'll find new and clever ways to use the environments.
Also I answered yes to all 3 questions.
The explanation on how the params stay linked even which switching environments was very helpful.
What concerns me is development. We do all development on separate computers using different instances of SQL Server for each developer and we utilize our own local paths for storing source files and such.
With the previous Package Configurations they would be applied when the package was being opened in BIDS so when I opened something on MY computer all the values would be pointing to my local resources because I setup these values in my local SQL Server configuration tables.
Now with SSDT if I start a project then another developer opens it they will need to manually change the parameters in the package so they can work against their own dev environment. Do you know of any way around this like some sort of local environment that is built in to SSDT? Of course we could all coordinate how we store things locally and always use (local) for SQL connections.
August 20, 2014 at 11:43 pm
Tom Van Harpen (8/20/2014)
Thanks for the response Phil.I am starting to see some benefits and after we get rolling I'm sure we'll find new and clever ways to use the environments.
Also I answered yes to all 3 questions.
The explanation on how the params stay linked even which switching environments was very helpful.
What concerns me is development. We do all development on separate computers using different instances of SQL Server for each developer and we utilize our own local paths for storing source files and such.
With the previous Package Configurations they would be applied when the package was being opened in BIDS so when I opened something on MY computer all the values would be pointing to my local resources because I setup these values in my local SQL Server configuration tables.
Now with SSDT if I start a project then another developer opens it they will need to manually change the parameters in the package so they can work against their own dev environment. Do you know of any way around this like some sort of local environment that is built in to SSDT? Of course we could all coordinate how we store things locally and always use (local) for SQL connections.
If this has all boiled down to the question of developers having different local instances of SQL Server, then I do have a solution for you.
1) Get every developer to create two new instance aliases (one 32-bit, one 64-bit) on their machine. The aliases should have the same name for all developers (both 32- and 64-bit) and should point to the local instance on that machine.
(Create the aliases in SQL Server Configuration Manager / SQL Native Client 11.0 Configuration (x2))
2) Modify your package connection strings to use the common alias name.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply