Migrating SSIS Projects and Parameters, Part 1

Question

Migrating SSIS Projects and Parameters, Part 1

Andy Leonard

SSChampion

Points: 10134
More actions
June 20, 2016 at 11:55 pm

#317292

Comments posted to this topic are about the item Migrating SSIS Projects and Parameters, Part 1
Andy Leonard, Chief Data Engineer, Enterprise Data & Analytics

Viewing 4 posts - 1 through 3 (of 3 total)

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

Phil Parkin SSC Guru Points: 246695 More actions · Answer 1

Well done for tackling this topic, Andy.

I use SSISDB environments a lot, but have never used parameter overrides. Do you have a view on when they should be used in preference to environment variables?

Note also a typo near the end, where 'file' has been written as 'pile'.

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

Andy Leonard SSChampion Points: 10134 More actions · Answer 2

Phil Parkin (6/21/2016)Do you have a view on when they should be used in preference to environment variables?

Thanks Phil!

Environment Variables are easier to change and appear (to me) to be built to support some changes. Overrides seem to me to be more Catalog-instance-specific and semi-permanent in nature. Can you use overrides or environment variables to externalize values? Yep. Catalog Compare makes both pretty easy to manage. When I think, "Why is this here?" - especially with regards to overrides - my best answer is, "For values I want to 'medium-code' (not hard code, and not externalize using environments) for this project in this Catalog." Does that make sense?

Perhaps an example of how I use overrides will help. I have a Parent package that is the package execution engine for my framework. Parent.dtsx has a single connection manager aimed at SSISDB. I use an override to "medium-code" the connection string so that Parent.dtsx is aimed at the local instance of SSISDB. I don't want this value sitting in an Environment Variable because those values look like they can and should be changed. I don't ever want this value to be changed. If someone is going to change it, I want to make that exercise as difficult as possible.

Thank you,

Andy

PS - I corrected the typo and re-aimed the links to SSIS Catalog Compare to a better site (DILMSuite.com) while I was in there! Steve will get the update posted soon! :{>

Andy Leonard, Chief Data Engineer, Enterprise Data & Analytics

Phil Parkin SSC Guru Points: 246695 More actions · Answer 3

Andy Leonard (6/21/2016)
Phil Parkin (6/21/2016)Do you have a view on when they should be used in preference to environment variables?
Thanks Phil!
Environment Variables are easier to change and appear (to me) to be built to support some changes. Overrides seem to me to be more Catalog-instance-specific and semi-permanent in nature. Can you use overrides or environment variables to externalize values? Yep. Catalog Compare makes both pretty easy to manage. When I think, "Why is this here?" - especially with regards to overrides - my best answer is, "For values I want to 'medium-code' (not hard code, and not externalize using environments) for this project in this Catalog." Does that make sense?
Perhaps an example of how I use overrides will help. I have a Parent package that is the package execution engine for my framework. Parent.dtsx has a single connection manager aimed at SSISDB. I use an override to "medium-code" the connection string so that Parent.dtsx is aimed at the local instance of SSISDB. I don't want this value sitting in an Environment Variable because those values look like they can and should be changed. I don't ever want this value to be changed. If someone is going to change it, I want to make that exercise as difficult as possible.
Thank you,
Andy
PS - I corrected the typo and re-aimed the links to SSIS Catalog Compare to a better site (DILMSuite.com) while I was in there! Steve will get the update posted soon! :{>

Thank you, Andy. That all makes sense.

I handle the example scenario you mention somewhat differently. I have a separate project for package execution/control & it has its own environment. Should anyone change any of those values to point elsewhere, they'll face some challenging questions!

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.