Creating a Robust SSIS Development Environment using the SSIS Catalog

  • reinis

    SSC Enthusiast

    Points: 131

    Comments posted to this topic are about the item Creating a Robust SSIS Development Environment using the SSIS Catalog

  • Michael Lysons

    SSCertifiable

    Points: 6465

    Excellent, thank you. A very timely post for me 🙂

  • Phil Parkin

    SSC Guru

    Points: 243549

    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).

    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.

  • reinis

    SSC Enthusiast

    Points: 131

    Phil Parkin - Monday, July 31, 2017 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).

    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.

  • gmwmn

    SSC Journeyman

    Points: 86

    SSC Guru makes a good point! Seems like asking for trouble to use a single server. Thanks for the article!

  • Alvin Ramard

    SSC-Forever

    Points: 41190

    reinis - Monday, July 31, 2017 8:50 AM

    Phil Parkin - Monday, July 31, 2017 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).

    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.



    Alvin Ramard
    Memphis PASS Chapter[/url]

    All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

    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]

  • bmg002

    SSC-Insane

    Points: 22362

    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.

  • reinis

    SSC Enthusiast

    Points: 131

    bmg002 - Monday, July 31, 2017 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.

    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.

  • bmg002

    SSC-Insane

    Points: 22362

    reinis - Monday, July 31, 2017 9:43 AM

    bmg002 - Monday, July 31, 2017 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.

    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.

  • reinis

    SSC Enthusiast

    Points: 131

    bmg002 - Monday, July 31, 2017 10:27 AM

    reinis - Monday, July 31, 2017 9:43 AM

    bmg002 - Monday, July 31, 2017 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.

    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).

  • bmg002

    SSC-Insane

    Points: 22362

    reinis - Monday, July 31, 2017 11:06 AM

    bmg002 - Monday, July 31, 2017 10:27 AM

    reinis - Monday, July 31, 2017 9:43 AM

    bmg002 - Monday, July 31, 2017 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.

    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.

  • corey lawson

    Hall of Fame

    Points: 3705

    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.

Viewing 12 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply