Migrating SSIS Projects and Parameters, Part 1

Question

Migrating SSIS Projects and Parameters, Part 1

Andy Leonard

SSChampion

Points: 10144
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: 246960 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'.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Andy Leonard SSChampion Points: 10144 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: 246960 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!

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin