Hi,

I would personally suggest you not depend on SSIS to accomplish this work. What you can do is add all stuff in Stored Procedure (keep Best Practice cases in mind) and call it in SSIS Execute SQL Task . Not only will this perform much better, it will also likely be less painful to develop.

However, you can do all this in SSIS also but Transformation may take too long if you have high volume of data.