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

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.
Paul MaurielloSoftware Programmer Developer Analyst EngineerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

lcohanDatabase AnalystCommented:
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
0
Paul MaurielloSoftware Programmer Developer Analyst EngineerAuthor 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?
0
lcohanDatabase AnalystCommented:
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)
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Paul MaurielloSoftware Programmer Developer Analyst EngineerAuthor 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?
0
lcohanDatabase AnalystCommented:
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
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Paul MaurielloSoftware Programmer Developer Analyst EngineerAuthor Commented:
Thank you!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Business Intelligence (BI)

From novice to tech pro — start learning today.