March 5, 2015 at 8:52 am
I am fairly new to SSIS and don't know a whole lot about it. I have been trying to follow an article on using SQL Server 2012 SSISDB with the Integration Services Catalog and 2012 VS SSDT to upgrade an older 2005 package that does some basic ETL work. My problem is that we only needed the one package for production using two connection strings throughout the Data Model to pull data from one database, transform it, and load to second database on the same SQL server, but now, after upgrading to 2012, we have four separate dedicated environments, Development (DEV), System Test (SYT), User Acceptance (UAT), and Production. I need to be able to create a single project/package in development that the DBA's can deploy to the other three environments by only updating in one place to indicate environment server without having to change multiple connections all over the place in the Data Model.
I believe what I need is to design a package for the DEV environment using two project parameters for the two databases and with four environment variables for each parameter in the Catalog, where each the variables indicate the specific server for each of the four environments (DEV, SYT, UAT, or Prod). However, my DBA, who has never done this either, is telling me I just need have two environment variables for the two databases and they will create a new package to deploy to the each of the four environment servers. Which of us correct? and once I have it defined correctly, I am still at a loss as how the Data Model object is configured to use the parameter/variable instead of an explicit connection string for each object. Can someone please point me in the right direction?
"Any fool can write code that a computer can understand. Good programmers write code that humans can understand." -- Martin Fowler
March 5, 2015 at 9:06 am
Ron Kunce (3/5/2015)
I am fairly new to SSIS and don't know a whole lot about it. I have been trying to follow an article on using SQL Server 2012 SSISDB with the Integration Services Catalog and 2012 VS SSDT to upgrade an older 2005 package that does some basic ETL work. My problem is that we only needed the one package for production using two connection strings throughout the Data Model to pull data from one database, transform it, and load to second database on the same SQL server, but now, after upgrading to 2012, we have four separate dedicated environments, Development (DEV), System Test (SYT), User Acceptance (UAT), and Production. I need to be able to create a single project/package in development that the DBA's can deploy to the other three environments by only updating in one place to indicate environment server without having to change multiple connections all over the place in the Data Model.I believe what I need is to design a package for the DEV environment using two project parameters for the two databases and with four environment variables for each parameter in the Catalog, where each the variables indicate the specific server for each of the four environments (DEV, SYT, UAT, or Prod). However, my DBA, who has never done this either, is telling me I just need have two environment variables for the two databases and they will create a new package to deploy to the each of the four environment servers. Which of us correct? and once I have it defined correctly, I am still at a loss as how the Data Model object is configured to use the parameter/variable instead of an explicit connection string for each object. Can someone please point me in the right direction?
It sounds to me like you are both wrong. Your DBA may be right but if so it is worded kind of ambiguously.
When you deploy a project to the catalog, you CONFIGURE it. This includes configuring the connections.
So you can develop your package with the connections hard coded for dev. and then deploy it.
Then you set up 4 environments. For those environments, you set up variables for the server names (and if not windows auth, user name and password) that would be used to run the package for that environment. All environments will use the same variable names but different values.
Then you configure the project and associate all 4 environments with it.
Then you configure the project/package again (depending on what level your connections are at), and go to configure the connections, and associate the server string with the environment variable that matches it.
Then when you execute, you specify which environment to run it against.
You can also do this with parameter values, but for connection strings, you don't need to.
March 5, 2015 at 9:24 am
I made an empty sample package with a connection manager, and a sample catalog so I could give you some screenshots.
They are attached.
EnvironmentVariable shows setting up a variable called 'ServerName' for development with the value 'Server1'. I set up a variable of the same name for production with the value 'Server2'
References shows me adding references to both environments to the project I deployed.
SettingConnectiontoUseServerName shows me setting up the relationship.
To get there, I selected the package, clicked Configure, went to the connection managers tab, clicked the '...' next to the ServerName property, selected "Use Environment Variable' from the radio group and selected it.
You can use the environments to pass values to project parameters too (I have done this for other purposes), and could use that for your connection strings if you really wanted to, but its not necessary here.
If you change your package and redeploy later, it should 'remember' your configuration and maintain it, so it will not overwrite your connections.
March 5, 2015 at 10:10 am
Edited -- I was sending this before I saw your second reply with examples. I will take a look at them. -- text removed.
"Any fool can write code that a computer can understand. Good programmers write code that humans can understand." -- Martin Fowler
March 5, 2015 at 10:34 am
Thank You much! I'm Sorry, I got environments and environment variables mixed up in my first post. I also specified "Data Model" where I meant "Data Flow". From what you sent, I already got that from the article I referenced. However because the article uses only a single Connection Manager set to connect to the master database on the server, I am still confused on what to do with the packages Data Flow objects where they would normally invoke an OLE DB Source Editor with a selected Connection Manager(CM) containing an explicit connection string to a database on the server (and which is what I am trying to avoid). How do I change that to use an environment variable instead, or do I? When Parameterizeing, this CM as a Project parameter do I create two parameters replacing the CM's Initial Catalog of "master" with desired database name?
"Any fool can write code that a computer can understand. Good programmers write code that humans can understand." -- Martin Fowler
March 5, 2015 at 10:57 am
Nevyn, the idea of having multiple environments in the same SSISDB instance for Dev/QA/Prod etc makes me shudder.
There is too much risk of running a package with the wrong env and screwing things up.
Dev, QA, prod etc should have their own SSISDB instances, in my opinion, to reduce the likelihood of needing to search for another job at short notice 😀
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.
March 5, 2015 at 11:00 am
Ron, you should try this out in on a test server.
There is no problem at all in overriding the connection string of a connection manager using an environment. No parametrization is required.
If you have a SQL user & need to insert a password in there somewhere, there are some nuances that we can work through. Other than that, it's straightforward.
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.
March 5, 2015 at 11:13 am
Ron Kunce (3/5/2015)
Thank You much! I'm Sorry, I got environments and environment variables mixed up in my first post. I also specified "Data Model" where I meant "Data Flow". From what you sent, I already got that from the article I referenced. However because the article uses only a single Connection Manager set to connect to the master database on the server, I am still confused on what to do with the packages Data Flow objects where they would normally invoke an OLE DB Source Editor with a selected Connection Manager(CM) containing an explicit connection string to a database on the server (and which is what I am trying to avoid). How do I change that to use an environment variable instead, or do I? When Parameterizeing, this CM as a Project parameter do I create two parameters replacing the CM's Initial Catalog of "master" with desired database name?
You can either follow the steps I said for server name and also have an environment variable for Initial Catalog, or if you prefer, you can actually make the connection string the environment variable.
March 5, 2015 at 11:21 am
Phil Parkin (3/5/2015)
Nevyn, the idea of having multiple environments in the same SSISDB instance for Dev/QA/Prod etc makes me shudder.There is too much risk of running a package with the wrong env and screwing things up.
Dev, QA, prod etc should have their own SSISDB instances, in my opinion, to reduce the likelihood of needing to search for another job at short notice 😀
Point taken.
The steps are the same, however. Just in that model you'd deploy to the multiple DBs which each had one environment, and configure each.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply