SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Scripting Environments in SSIS


Scripting Environments in SSIS

Author
Message
jordan.jeffrey
jordan.jeffrey
SSC Veteran
SSC Veteran (239 reputation)SSC Veteran (239 reputation)SSC Veteran (239 reputation)SSC Veteran (239 reputation)SSC Veteran (239 reputation)SSC Veteran (239 reputation)SSC Veteran (239 reputation)SSC Veteran (239 reputation)

Group: General Forum Members
Points: 239 Visits: 294
Comments posted to this topic are about the item Scripting Environments in SSIS
Tim ffitch
Tim ffitch
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1235 Visits: 308
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
Koen Verbeeck
SSC Guru
SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)

Group: General Forum Members
Points: 217878 Visits: 13362
You sir are my hero!
I only wish this article was published yesterday, when I was creating environments on the new production server :-D


How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
Koen Verbeeck
Koen Verbeeck
SSC Guru
SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)

Group: General Forum Members
Points: 217878 Visits: 13362
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.


How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
Tim ffitch
Tim ffitch
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1235 Visits: 308
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
jordan.jeffrey
SSC Veteran
SSC Veteran (239 reputation)SSC Veteran (239 reputation)SSC Veteran (239 reputation)SSC Veteran (239 reputation)SSC Veteran (239 reputation)SSC Veteran (239 reputation)SSC Veteran (239 reputation)SSC Veteran (239 reputation)

Group: General Forum Members
Points: 239 Visits: 294
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 :-D


Keon, I appreaciate that you took the time to read my my post and would find it useful.
Koen Verbeeck
Koen Verbeeck
SSC Guru
SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)

Group: General Forum Members
Points: 217878 Visits: 13362
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 :-D


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 :-D

Thanks again!


How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
Koen Verbeeck
Koen Verbeeck
SSC Guru
SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)

Group: General Forum Members
Points: 217878 Visits: 13362
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.


How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
Phil Parkin
Phil Parkin
SSC Guru
SSC Guru (194K reputation)SSC Guru (194K reputation)SSC Guru (194K reputation)SSC Guru (194K reputation)SSC Guru (194K reputation)SSC Guru (194K reputation)SSC Guru (194K reputation)SSC Guru (194K reputation)

Group: General Forum Members
Points: 194281 Visits: 23764
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.


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

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.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
stan.geiger
stan.geiger
Mr or Mrs. 500
Mr or Mrs. 500 (512 reputation)Mr or Mrs. 500 (512 reputation)Mr or Mrs. 500 (512 reputation)Mr or Mrs. 500 (512 reputation)Mr or Mrs. 500 (512 reputation)Mr or Mrs. 500 (512 reputation)Mr or Mrs. 500 (512 reputation)Mr or Mrs. 500 (512 reputation)

Group: General Forum Members
Points: 512 Visits: 119
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search