Best way to resolve SSIS METADATA issues when copying out packages?

Paul Mauriello
Paul Mauriello used Ask the Experts™
on
I have a situation where i need to reproduce a complex SSIS package with varying degrees of differences but for the most part 60% of it is the same across all permutations.

The problem being is each one needs to be recreated from scratch. If any copy and paste is done then it results in METADATA errors when i test in the DEV environment but even more so when i deploy to production.

This quickly turns into a nightmare.

Has any one developed any clever ways to resolve the SSIS METADATA errors that doesn't require either creating each package from scratch  or opening, building, saving, and closing each and every task?

Thank you.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
lcohanDatabase Analyst

Commented:
In my opinion the best and easiest way for that matter would be to enable and use configuration files - see tutorial here: https://docs.microsoft.com/en-us/sql/integration-services/lesson-5-2-enabling-and-configuring-package-configurations?view=sql-server-2017
We're doing exactly the same to develop packages in dev/qa environment and deploy to prod where only changes done are not inside the SSIS DTSX file but the configuration file editable with notepad/word-pad.
https://docs.microsoft.com/en-us/sql/integration-services/lesson-1-4-adding-package-configurations?view=sql-server-2014
Paul MaurielloSoftware Programmer Developer Analyst Engineer

Author

Commented:
I appreciate your response but we already have a configuration file setup that determine environment and even certain values and database variables. But we are talking slight differences in structuring the data tasks that need to be made depending on the company. I don't see how based on these examples and the method you described would help us. How would you put differing whole data tasks in a configuration file?
lcohanDatabase Analyst

Commented:
Just so I understand exactly what you mean - is this meaning that the underlying SQL structure is different
depending on the company.
or rather the SSIS package is different
depending on the company.

I incline to believe that what you need is something like a "dynamic SSIS" package depending on the company - is this what you actually mean? (forgive my poor English, I couldn't find better comparison)
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Paul MaurielloSoftware Programmer Developer Analyst Engineer

Author

Commented:
Exactly i need to be a great deal more dynamic but they way i keep running into these metadata issues, even using the suggestions you gave me, it is not dynamic enough? It makes me feel that SSIS is not capable of the level of dynamic-ness that i would need it to be? what are your thoughts?
Database Analyst
Commented:
Yes indeed the suggestions I gave would not help to build a "dynamic SSIS". For that matter depending how dynamic and what you need to specifically make dynamic in the SSIS you can go from programmatically generate the SSIS package https://docs.microsoft.com/en-us/sql/integration-services/building-packages-programmatically/building-packages-programmatically?view=sql-server-2014 to using 3rd party software or maybe simpler things like build your own mapping tables - see few ideas here https://social.msdn.microsoft.com/Forums/sqlserver/en-US/71a1a7b6-3245-4298-86e4-227e63c67927/dynamic-column-mapping-in-ssis?forum=sqlintegrationservices
Paul MaurielloSoftware Programmer Developer Analyst Engineer

Author

Commented:
Thank you!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial