SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SSIS Environments and Project Parameters Conversion from 2008 Configs


SSIS Environments and Project Parameters Conversion from 2008 Configs

Author
Message
Tom Van Harpen
Tom Van Harpen
SSC Eights!
SSC Eights! (886 reputation)SSC Eights! (886 reputation)SSC Eights! (886 reputation)SSC Eights! (886 reputation)SSC Eights! (886 reputation)SSC Eights! (886 reputation)SSC Eights! (886 reputation)SSC Eights! (886 reputation)

Group: General Forum Members
Points: 886 Visits: 1102
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.
Phil Parkin
Phil Parkin
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

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


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
Tom Van Harpen
Tom Van Harpen
SSC Eights!
SSC Eights! (886 reputation)SSC Eights! (886 reputation)SSC Eights! (886 reputation)SSC Eights! (886 reputation)SSC Eights! (886 reputation)SSC Eights! (886 reputation)SSC Eights! (886 reputation)SSC Eights! (886 reputation)

Group: General Forum Members
Points: 886 Visits: 1102
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.
Phil Parkin
Phil Parkin
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

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


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
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