July 31, 2017 at 12:07 am
Comments posted to this topic are about the item Creating a Robust SSIS Development Environment using the SSIS Catalog
July 31, 2017 at 3:36 am
Excellent, thank you. A very timely post for me 🙂
July 31, 2017 at 6:39 am
You have written a very clear and concise article to demonstrate how you have put things together and I congratulate you for that. Sounds like you have a solution which works well.
But at the heart of what you are describing lays a practice which I think is not so good: you appear to be tacitly advocating the use of a single server to manage Dev/QA/Prod environments.
In the 2014 and above world, where Developer Edition is free, there should be little need to do this.
Unless your management controls are extremely tight (and I have no reason to doubt otherwise), one day, someone is going to make a mistake and some non-production code/data is going to find its way into production.
Better practice, in my opinion, is to keep the Prod server separate and to set things up such that packages deployed to Dev and QA cannot possibly touch prod (through the use of appropriate service accounts).
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
July 31, 2017 at 8:50 am
Phil Parkin - Monday, July 31, 2017 6:39 AMYou have written a very clear and concise article to demonstrate how you have put things together and I congratulate you for that. Sounds like you have a solution which works well.
But at the heart of what you are describing lays a practice which I think is not so good: you appear to be tacitly advocating the use of a single server to manage Dev/QA/Prod environments.
In the 2014 and above world, where Developer Edition is free, there should be little need to do this.
Unless your management controls are extremely tight (and I have no reason to doubt otherwise), one day, someone is going to make a mistake and some non-production code/data is going to find its way into production.
Better practice, in my opinion, is to keep the Prod server separate and to set things up such that packages deployed to Dev and QA cannot possibly touch prod (through the use of appropriate service accounts).
Yes that's quite true, and I certainly agree that completely separate environments would be the best practice and safest way. I suspect that there are also still a lot of smaller shops that are still working with limited-scope environments that may be able to utilize some of these options.
July 31, 2017 at 8:53 am
SSC Guru makes a good point! Seems like asking for trouble to use a single server. Thanks for the article!
July 31, 2017 at 8:54 am
reinis - Monday, July 31, 2017 8:50 AMPhil Parkin - Monday, July 31, 2017 6:39 AMYou have written a very clear and concise article to demonstrate how you have put things together and I congratulate you for that. Sounds like you have a solution which works well.
But at the heart of what you are describing lays a practice which I think is not so good: you appear to be tacitly advocating the use of a single server to manage Dev/QA/Prod environments.
In the 2014 and above world, where Developer Edition is free, there should be little need to do this.
Unless your management controls are extremely tight (and I have no reason to doubt otherwise), one day, someone is going to make a mistake and some non-production code/data is going to find its way into production.
Better practice, in my opinion, is to keep the Prod server separate and to set things up such that packages deployed to Dev and QA cannot possibly touch prod (through the use of appropriate service accounts).Yes that's quite true, and I certainly agree that completely separate environments would be the best practice and safest way. I suspect that there are also still a lot of smaller shops that are still working with limited-scope environments that may be able to utilize some of these options.
I agree.
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
July 31, 2017 at 9:28 am
What we do where I work is have the environment set up for the connection managers instead of for an environment. That way, anything we deploy to test, regardless of what is set in Visual studio, will connect to test (unless we update the environment). And live will always point to live.
The other advantage of this is if you are changing SQL Server instances (for example, a software upgrade requires a SQL upgrade but you want to keep the old instance for failover. Or you are implementing a data warehouse and moving live data to that server), it is much easier to go into the environment and change the connection string value to be the new server than to open up each SSIS project and adjust the connection string.
We also have several test/dev environments that we mix and match different SQL environments to. So if the developer is testing an SSIS package, they just need to load it into the test SSIS catalog, change the environment to the proper server set, and hit go without having to build in a bunch of different test configurations.
Right now, we are in the process of upgrading some software which requires us to upgrade SQL as well. This will result in the SQL instance getting a brand new name. With our setup, we only need to adjust 1 environment and we will be good to go. With your setup, we would need to alter almost all of our SSIS packages; and we have about 100 on our server.
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.
July 31, 2017 at 9:43 am
bmg002 - Monday, July 31, 2017 9:28 AMWhat we do where I work is have the environment set up for the connection managers instead of for an environment. That way, anything we deploy to test, regardless of what is set in Visual studio, will connect to test (unless we update the environment). And live will always point to live.The other advantage of this is if you are changing SQL Server instances (for example, a software upgrade requires a SQL upgrade but you want to keep the old instance for failover. Or you are implementing a data warehouse and moving live data to that server), it is much easier to go into the environment and change the connection string value to be the new server than to open up each SSIS project and adjust the connection string.
We also have several test/dev environments that we mix and match different SQL environments to. So if the developer is testing an SSIS package, they just need to load it into the test SSIS catalog, change the environment to the proper server set, and hit go without having to build in a bunch of different test configurations.
Right now, we are in the process of upgrading some software which requires us to upgrade SQL as well. This will result in the SQL instance getting a brand new name. With our setup, we only need to adjust 1 environment and we will be good to go. With your setup, we would need to alter almost all of our SSIS packages; and we have about 100 on our server.
Forgive me if I'm not quite clear on your setup, but that does sound like the same setup as ours once a package is deployed to the catalog - if the production server name is changed, we only have to change the Environment Variable "ServerName" in the PROD folder in the Catalog in one place and all the packages in the PROD folder will be updated.
July 31, 2017 at 10:27 am
reinis - Monday, July 31, 2017 9:43 AMbmg002 - Monday, July 31, 2017 9:28 AMWhat we do where I work is have the environment set up for the connection managers instead of for an environment. That way, anything we deploy to test, regardless of what is set in Visual studio, will connect to test (unless we update the environment). And live will always point to live.The other advantage of this is if you are changing SQL Server instances (for example, a software upgrade requires a SQL upgrade but you want to keep the old instance for failover. Or you are implementing a data warehouse and moving live data to that server), it is much easier to go into the environment and change the connection string value to be the new server than to open up each SSIS project and adjust the connection string.
We also have several test/dev environments that we mix and match different SQL environments to. So if the developer is testing an SSIS package, they just need to load it into the test SSIS catalog, change the environment to the proper server set, and hit go without having to build in a bunch of different test configurations.
Right now, we are in the process of upgrading some software which requires us to upgrade SQL as well. This will result in the SQL instance getting a brand new name. With our setup, we only need to adjust 1 environment and we will be good to go. With your setup, we would need to alter almost all of our SSIS packages; and we have about 100 on our server.Forgive me if I'm not quite clear on your setup, but that does sound like the same setup as ours once a package is deployed to the catalog - if the production server name is changed, we only have to change the Environment Variable "ServerName" in the PROD folder in the Catalog in one place and all the packages in the PROD folder will be updated.
Maybe I am misreading your setup, but it looks like your environment in the article is just storing the RuntimeEnvironment variable is it not? The setup we have in place, we store the connection string in the environment instead of the runtime environment. So a change to a connection string doesn't require opening up each package in visual studio.
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.
July 31, 2017 at 11:06 am
bmg002 - Monday, July 31, 2017 10:27 AMreinis - Monday, July 31, 2017 9:43 AMbmg002 - Monday, July 31, 2017 9:28 AMWhat we do where I work is have the environment set up for the connection managers instead of for an environment. That way, anything we deploy to test, regardless of what is set in Visual studio, will connect to test (unless we update the environment). And live will always point to live.The other advantage of this is if you are changing SQL Server instances (for example, a software upgrade requires a SQL upgrade but you want to keep the old instance for failover. Or you are implementing a data warehouse and moving live data to that server), it is much easier to go into the environment and change the connection string value to be the new server than to open up each SSIS project and adjust the connection string.
We also have several test/dev environments that we mix and match different SQL environments to. So if the developer is testing an SSIS package, they just need to load it into the test SSIS catalog, change the environment to the proper server set, and hit go without having to build in a bunch of different test configurations.
Right now, we are in the process of upgrading some software which requires us to upgrade SQL as well. This will result in the SQL instance getting a brand new name. With our setup, we only need to adjust 1 environment and we will be good to go. With your setup, we would need to alter almost all of our SSIS packages; and we have about 100 on our server.Forgive me if I'm not quite clear on your setup, but that does sound like the same setup as ours once a package is deployed to the catalog - if the production server name is changed, we only have to change the Environment Variable "ServerName" in the PROD folder in the Catalog in one place and all the packages in the PROD folder will be updated.
Maybe I am misreading your setup, but it looks like your environment in the article is just storing the RuntimeEnvironment variable is it not? The setup we have in place, we store the connection string in the environment instead of the runtime environment. So a change to a connection string doesn't require opening up each package in visual studio.
The RuntimeEnvironment does indeed store the environment - however, that is used only by control of flow and file paths so that the environment variable can influence how flow occurs (e.g. in PROD you want an email sent to everyone, but in TEST only to yourself - you can do that by setting up two different flow paths with precedence constraints, for example) once you have deployed to the catalog. The RuntimeEnvironment itself would not be used for connection managers to servers.
Connection managers use a project parameter specifically created for each of their connection options, and those parameters are then mapped to Environment variables - for example, the connection manager SQL1 may use the project parameters SQL1ServerName, SQL1UserName, SQL1InitialCatalog, etc. During initial deploy these parameters are mapped to environment variables (EV_SQL1ServerName, EV_SQL1UserName and EV_SQL1InitialCatalog).
July 31, 2017 at 11:56 am
reinis - Monday, July 31, 2017 11:06 AMbmg002 - Monday, July 31, 2017 10:27 AMreinis - Monday, July 31, 2017 9:43 AMbmg002 - Monday, July 31, 2017 9:28 AMWhat we do where I work is have the environment set up for the connection managers instead of for an environment. That way, anything we deploy to test, regardless of what is set in Visual studio, will connect to test (unless we update the environment). And live will always point to live.The other advantage of this is if you are changing SQL Server instances (for example, a software upgrade requires a SQL upgrade but you want to keep the old instance for failover. Or you are implementing a data warehouse and moving live data to that server), it is much easier to go into the environment and change the connection string value to be the new server than to open up each SSIS project and adjust the connection string.
We also have several test/dev environments that we mix and match different SQL environments to. So if the developer is testing an SSIS package, they just need to load it into the test SSIS catalog, change the environment to the proper server set, and hit go without having to build in a bunch of different test configurations.
Right now, we are in the process of upgrading some software which requires us to upgrade SQL as well. This will result in the SQL instance getting a brand new name. With our setup, we only need to adjust 1 environment and we will be good to go. With your setup, we would need to alter almost all of our SSIS packages; and we have about 100 on our server.Forgive me if I'm not quite clear on your setup, but that does sound like the same setup as ours once a package is deployed to the catalog - if the production server name is changed, we only have to change the Environment Variable "ServerName" in the PROD folder in the Catalog in one place and all the packages in the PROD folder will be updated.
Maybe I am misreading your setup, but it looks like your environment in the article is just storing the RuntimeEnvironment variable is it not? The setup we have in place, we store the connection string in the environment instead of the runtime environment. So a change to a connection string doesn't require opening up each package in visual studio.
The RuntimeEnvironment does indeed store the environment - however, that is used only by control of flow and file paths so that the environment variable can influence how flow occurs (e.g. in PROD you want an email sent to everyone, but in TEST only to yourself - you can do that by setting up two different flow paths with precedence constraints, for example) once you have deployed to the catalog. The RuntimeEnvironment itself would not be used for connection managers to servers.
Connection managers use a project parameter specifically created for each of their connection options, and those parameters are then mapped to Environment variables - for example, the connection manager SQL1 may use the project parameters SQL1ServerName, SQL1UserName, SQL1InitialCatalog, etc. During initial deploy these parameters are mapped to environment variables (EV_SQL1ServerName, EV_SQL1UserName and EV_SQL1InitialCatalog).
Ok, then I think we have it set up in a similar manner. Good to know that I wasn't doing things too crazy.
We still need to upgrade to SSIS 2016 so we can deploy a single dtsx inside of a project instead of having to deploy an entire project at a time, but that isn't too hard to do. Just time consuming and will require testing.
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.
July 31, 2017 at 4:35 pm
Hmm.... at a previous job, we used config files (the xml ones) to at least deal with connections - one file per resource (e.g., server+database).
The convention we used was that the SSIS servers or where they were run from had a local drive folder where their config files were stored. As developers and testers, we also had the same "local" drive (either via net use or subst), but our dev or test config files were in there - something like "C:\SSIS_Configs\". the config files were named the same throughout.
the ops people could keep their production passwords safe, with no one embedding passwords in SSIS packages, etc, and us devs and QA testers just had to copy the SSIS packages into our respective environments, whether it was our desktops or dev or testing servers. It all Just Worked, especially as it was one less thing to remember to check or fix when deploying packages up the chain to production.
For my SSIS packages, I prefer to use expressions and package variables where I can to "dynamically" twiddle these kinds of things. At the very least, while the command-line (DTEXEC) syntax for setting values to package variables is painful, it also just works... It could be that those package-level variables may need to be dynamically set at the command line as well. For example, our ops system could return certain date range values (like 1st of month, 7th business day of month, etc). Those values could be picked out in the specific job and then passed into the packages easily enough, meaning these were defined and maintained in one place.
YMWV.
January 7, 2020 at 3:11 pm
Thanks for sharing this info. Could you elaborate on how you created [SSISFilestore_Root] (figure 11) relative references in SSIS catalog?
June 12, 2021 at 7:04 am
This was removed by the editor as SPAM
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply