SQL Server SSIS Transformation Assistance

I have an SSIS package already pulling data in and pushing it out; where I need an experienced set of eyes is the Transform stage that sits between those two points. The source and target tables are ready, but the intermediate logic is getting unwieldy, and performance has started to dip.

Your task is to step into the existing SQL Server + SSIS environment, review the current transformation workflow, and help me refactor or rebuild it so that the package runs cleanly, quickly, and is easy to maintain. We may end up incorporating additional look-ups, conditional splits, or script-based transformations—whatever proves most effective once you have assessed the current design.

I will provide:
• The .dtsx package and solution files
• Sample data and schema scripts for the staging and destination databases
• A quick rundown of the business rules that must be respected

You will deliver an updated package (or clear instructions and code snippets) that:
1. Implements the required transformation logic correctly
2. Minimises execution time and memory usage
3. Follows best practices for readability and error handling

If you are comfortable diagnosing complex data flows and optimising transformations inside SSIS, I would love to get this done together soon.