Changing Connection Managers with the Configuration Manager
-
OK, this actually not a question, this is in response to a question I got in DM from Phil Parkin, and answering easily is much harder there. That does not, however, mean you can't ask me questions about what I'm explaining here. Also, please excuse the low quality of any screen shots, as it's over my awful internet and a remote session.
OK, so something I use in SSIS in almost all our projects is Project Parmeters that have their values set depending on with Configuration I'm in. This is really useful, at least in my work environment, as with just a drop down list I can change multiple values of project parameters, that are also bound to certain objects, such as connection managers, within the project. For example, in the below, you can see that I can from Live, UAT, and Development, and the names of the SQL Server Parameter changes. This in turn changes the Server that the OLEDB Connection Managers connect to:
Other projects will also have parameters with the URL of Web Services, Host Names of our Broking system, etc. Meaning that these are changed through the project with literally just a drop down menu (after set up).
Firstly, to achieve this, we need to create the configurations. This is in the above drop down in the image, and select "Configuration Manager...". This should already one configuration, however, let's add a few more. Select "<New...>" in Active solution configuration. I'm going to add 3 new configurations, Testing, Local and Production. As the project has no prior settings on the configurations, you don't need to copy the settings from anything else (but if you were adding them at a later date, you might well want to).
You should then end up with something like this:
Now I'm going to go to the Project.Params file, and add a new parameter, SQLInstanceName, with the data type String. Then open the Add Parameters to Configurations Menu (it's the box with a spanner). Click Add and add SQLInstanceName. Then enter the names of your instances for the relevant environments. So I'm going to use SQL01 for Production, SQL02/UAT for Testing, SQL02/DEV for Development. You then have something like this:
You can test if the settings have applied, by then switching the configuration.
To then have a Connection Manager use this in it's Connection String you first need to create one (duh!). Set this up as normal, specifying an actual Instance you have. To actually make the connection manager use an expression for a property though, you (oddly) have to be editing a package. If you don't have a package in the project, create one. At the bottom you'll see the Connection Managers pane with your project SQL Connection manager. Select that and hit F4/Right Click->Properties for the Properties Pane to be opened for the Connection Manager. Locate Expressions, then click the blank box to the right and then the ellipsis (...). For Property, select ServerName, and then for the Expression into your Project Parameter you set up previously, in this example that's
@[$Project::SQLInstanceName], and then ok your changesNow your SQL Connection Manager will show a little fx symbol to the left, to show it is "dynamic". Now, when you change the configuration you are in, the project parameter's value will be changed, and along side it the value of ServerName in the Connection manager, meaning that depending on the configuration you're in, the SQL instance your Visual Studio Project connects to will change.
- This topic was modified 4 years, 5 months ago by Thom A.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk -
great explanation
should this be created as an article instead?
-
Good post - I have leveraged this for a while (this also works for deployment targets of SSRS/SSAS projects) but I'm always surprised more people don't use it. Nice one 🙂
I'm on LinkedIn -
Thom, this is great. Thank you for taking the time to write it out so clearly.
but I'm always surprised more people don't use it
Almost all of my development is done on my local instance (which I believe is generally regarded as best practice). In this case, setting default 'design time' values for parameters and variables is usually sufficient for me. I know that Thom is constrained to develop in a shared-instance environment and his need to easily switch parameter values has arisen from that.
But now that I've seen the technique demonstrated so well, no doubt I will find uses for it (eg, different values corresponding with different test case scenarios).
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
Not SQL related, but you can do similar things with C# and other .NET languages if you use Visual Studio.
I like doing this when I make an application in C# so I can connect to the dev servers during development, pass it over to the test servers for internal testing and UAT, and then change it over to prod for the release to live.
Configurations are awesome to work with when you can. The only downside to them that I know of is once YOU start using them, your entire development team should (MUST?) use them or things get messy quickly. All it takes is 1 developer who doesn't like them and changes the settings for the default configuration and push it to git and all your work making things easier is lost.
A similar concept, but a little different is environments in an SSIS catalog. Those things are a life saver! Modify 1 location and all of your SSIS packages get updated to point to the new instance. Great for when you need to do a SQL migration to a new server, or a migration upgrade, or if you need to re-run an ETL load but want it to go from live to test servers. Also nice for your "test" SSIS server for running the load on dev then on test then on UAT and QA. script out the job, find-replace the name of the environment with the new one and rename the job to indicate the environment it uses and you are ready to rock. Or you can just update the environment and you can fire it off against any server you like.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system. -
Hi Team, this is first time I am posting question on forum, I am able to connect to DB using SSMS and windows authentication, however when I am trying to connect in SSIS for new OLEDB connection it seems to give me error as below.
Test Connection failed because of an error in initializing provider. Client unable to establish connection TCP Provider: An existing connection was forcibly closed by the remote host.
Please respond. Thanks
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply