kramaswamy (1/23/2012)
One thing to be careful about whenever you're using dynamic SQL in SSIS - if you're ever thinking of putting that data into a destination table or file through a data flow task, and your source is using dynamic SQL, you *will* run in to problems.Since your solution isn't doing that, it shouldn't be an issue, but for anyone who is taking this idea and trying to adapt it to their own work, keep that in mind.
Why will you run into problems? The only time you his a problem is when you remove a column from the source. If you add columns its generally not a problem, or in my experience anyway.
As for your earlier comment about the SCD component, it promised much and is ok if you have a static design, but if you make a change you have a lot more work on your hands in regard to changing, retesting and deploying the new SSIS packages.
Not sure if thats changed in 2012 or not, i would guess the latter.
_________________________________________________________________________
SSC Guide to Posting and Best Practices