Scripting Environments in SSIS

  • jordan.jeffrey

    SSC Veteran

    Points: 277

    Comments posted to this topic are about the item Scripting Environments in SSIS

  • Tim ffitch

    SSCommitted

    Points: 1577

    I can see where this would be useful for multiple environments on the same server. When dealing with multiple servers then it would be better to create your environments in Data Tools/Visual Studio using package and project parameters with different values set up for each server. This is one areas where 2012 is so much better than 2008.

  • Koen Verbeeck

    SSC Guru

    Points: 258826

    You sir are my hero!

    I only wish this article was published yesterday, when I was creating environments on the new production server 😀

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • Koen Verbeeck

    SSC Guru

    Points: 258826

    Tim ffitch (1/21/2016)


    I can see where this would be useful for multiple environments on the same server. When dealing with multiple servers then it would be better to create your environments in Data Tools/Visual Studio using package and project parameters with different values set up for each server. This is one areas where 2012 is so much better than 2008.

    I'm not sure how you would specify different values in SSDT for each server?

    You mean by using "visual studio configurations"? Like in this article:

    https://www.mssqltips.com/sqlservertip/3513/using-visual-studio-configurations-in-sql-server-integration-services-projects/

    I still create one environment per server. So scripting the environment would be useful if I add a new server.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • Tim ffitch

    SSCommitted

    Points: 1577

    Koen Verbeeck (1/21/2016)


    Tim ffitch (1/21/2016)


    I can see where this would be useful for multiple environments on the same server. When dealing with multiple servers then it would be better to create your environments in Data Tools/Visual Studio using package and project parameters with different values set up for each server. This is one areas where 2012 is so much better than 2008.

    I'm not sure how you would specify different values in SSDT for each server?

    You mean by using "visual studio configurations"? Like in this article:

    https://www.mssqltips.com/sqlservertip/3513/using-visual-studio-configurations-in-sql-server-integration-services-projects/

    I still create one environment per server. So scripting the environment would be useful if I add a new server.

    Yes exactly that. It makes the management and deployment so much easier. You have to add the parameters into configurations, which is really easy, you can add them all in one go for each package or project at a time. You then have a grid window where you set the values for each configuration. In the deployment set up you set the target server, so when you deploy each server gets its correct configuration, so much easier.

  • jordan.jeffrey

    SSC Veteran

    Points: 277

    Koen Verbeeck (1/21/2016)


    You sir are my hero!

    I only wish this article was published yesterday, when I was creating environments on the new production server 😀

    Keon, I appreaciate that you took the time to read my my post and would find it useful.

  • Koen Verbeeck

    SSC Guru

    Points: 258826

    jordan.jeffrey (1/21/2016)


    Koen Verbeeck (1/21/2016)


    You sir are my hero!

    I only wish this article was published yesterday, when I was creating environments on the new production server 😀

    Keon, I appreaciate that you took the time to read my my post and would find it useful.

    Yes. I was frustrated for some time with the SSIS environments and the inability to script them.

    I knew I could use T-SQL or PowerShell to get all the info I needed from the catalog, but I never got around to actually writing such a script.

    So now I'm glad someone else did the work for me 😀

    Thanks again!

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • Koen Verbeeck

    SSC Guru

    Points: 258826

    Tim ffitch (1/21/2016)


    Koen Verbeeck (1/21/2016)


    Tim ffitch (1/21/2016)


    I can see where this would be useful for multiple environments on the same server. When dealing with multiple servers then it would be better to create your environments in Data Tools/Visual Studio using package and project parameters with different values set up for each server. This is one areas where 2012 is so much better than 2008.

    I'm not sure how you would specify different values in SSDT for each server?

    You mean by using "visual studio configurations"? Like in this article:

    https://www.mssqltips.com/sqlservertip/3513/using-visual-studio-configurations-in-sql-server-integration-services-projects/

    I still create one environment per server. So scripting the environment would be useful if I add a new server.

    Yes exactly that. It makes the management and deployment so much easier. You have to add the parameters into configurations, which is really easy, you can add them all in one go for each package or project at a time. You then have a grid window where you set the values for each configuration. In the deployment set up you set the target server, so when you deploy each server gets its correct configuration, so much easier.

    I know how the configurations work (I wrote the article btw ;-)), but I didn't know you could link them to deployments. So thanks for that useful piece of info. Must try it out soon.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • Phil Parkin

    SSC Guru

    Points: 243272

    Tim ffitch (1/21/2016)


    I can see where this would be useful for multiple environments on the same server. When dealing with multiple servers then it would be better to create your environments in Data Tools/Visual Studio using package and project parameters with different values set up for each server. This is one areas where 2012 is so much better than 2008.

    This is a useful technique for handling connections for development purposes. But does it give you everything that environments give you? A few potential negatives come to mind:

    1) Where you have many SSIS projects, if a config item (eg, a conn string) ever changes, this requires (potentially) only a single change in SSISDB when using an environment, not one change per project.

    2) Sensitive items are not supported.

    3) Config changes require code changes.

    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.

  • stan.geiger

    SSChasing Mays

    Points: 627

    This is exactly why when I initially create environments I create them in TSQL using the SPROCs from SSISDB. In fact I have scripted entire deployments from folders, environments, variables, and packages using the .ispac file. It makes it so much easier to recreate somewhere else or recover.

  • Tim ffitch

    SSCommitted

    Points: 1577

    Phil Parkin (1/21/2016)


    Tim ffitch (1/21/2016)


    I can see where this would be useful for multiple environments on the same server. When dealing with multiple servers then it would be better to create your environments in Data Tools/Visual Studio using package and project parameters with different values set up for each server. This is one areas where 2012 is so much better than 2008.

    This is a useful technique for handling connections for development purposes. But does it give you everything that environments give you? A few potential negatives come to mind:

    1) Where you have many SSIS projects, if a config item (eg, a conn string) ever changes, this requires (potentially) only a single change in SSISDB when using an environment, not one change per project.

    2) Sensitive items are not supported.

    3) Config changes require code changes.

    Shared configs are not always a good thing, it depends on individual circumstances and requirements.

    I have not had problems with Sensitive items. I have used it several times for passwords for connection strings.

    You can still make config changes within SSISDB without having to deploy again, I've not needed to do it myself but I know you can. The system does throw up a warning, the wording of which I can't remember when I was experimenting one day.

  • jordan.jeffrey

    SSC Veteran

    Points: 277

    Tim ffitch (1/21/2016)


    I can see where this would be useful for multiple environments on the same server. When dealing with multiple servers then it would be better to create your environments in Data Tools/Visual Studio using package and project parameters with different values set up for each server. This is one areas where 2012 is so much better than 2008.

    Tim, Thanks for taking the time to look at my post and commenting. I think we can probably all agree that there are different scenarios which require different approaches. At the basic level this is just another tool to put in your toolbox and use as needed.

  • Phil Parkin

    SSC Guru

    Points: 243272

    Tim ffitch (1/21/2016)


    Phil Parkin (1/21/2016)


    Tim ffitch (1/21/2016)


    I can see where this would be useful for multiple environments on the same server. When dealing with multiple servers then it would be better to create your environments in Data Tools/Visual Studio using package and project parameters with different values set up for each server. This is one areas where 2012 is so much better than 2008.

    This is a useful technique for handling connections for development purposes. But does it give you everything that environments give you? A few potential negatives come to mind:

    1) Where you have many SSIS projects, if a config item (eg, a conn string) ever changes, this requires (potentially) only a single change in SSISDB when using an environment, not one change per project.

    2) Sensitive items are not supported.

    3) Config changes require code changes.

    Shared configs are not always a good thing, it depends on individual circumstances and requirements.

    I have not had problems with Sensitive items. I have used it several times for passwords for connection strings.

    You can still make config changes within SSISDB without having to deploy again, I've not needed to do it myself but I know you can. The system does throw up a warning, the wording of which I can't remember when I was experimenting one day.

    I was comparing your preferred method of VS configurations, which do not hit SSISDB (other than directly within the deployed packages), with SSISDB Environments. I believe that my points remain valid.

    Your response appears to be addressing some other concerns & I'm not sure what they are.

    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.

  • akljfhnlaflkj

    SSC Guru

    Points: 76202

    Thanks for this.

  • nick.mcdermaid

    Hall of Fame

    Points: 3668

    In SSMS 2016 there is a script option in the UI if you do properties on the environment... but it doesn't do anything! It seems to be there fore decoration

Viewing 15 posts - 1 through 15 (of 30 total)

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