January 20, 2016 at 10:18 pm
Comments posted to this topic are about the item Scripting Environments in SSIS
January 21, 2016 at 2:36 am
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.
January 21, 2016 at 3:09 am
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
January 21, 2016 at 3:12 am
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:
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
January 21, 2016 at 4:09 am
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:
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.
January 21, 2016 at 5:30 am
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.
January 21, 2016 at 5:55 am
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
January 21, 2016 at 5:58 am
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:
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
January 21, 2016 at 6:18 am
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.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
January 21, 2016 at 7:34 am
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.
January 21, 2016 at 7:46 am
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.
January 21, 2016 at 8:35 am
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.
January 21, 2016 at 8:40 am
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.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
February 25, 2016 at 4:27 pm
Thanks for this.
December 15, 2016 at 10:50 pm
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 33 total)
You must be logged in to reply to this topic. Login to reply