Good Question. This is required as there will be changes made to the columns at Source (adding or dropping columns). If we go with SSIS, we need to do mapping too.
Please advice that SSIS is the better choice.
Now that is a much more complicated process...
I have had colleagues who have done this in the past successfully using SSIS but not
without some pretty complicated dynamic SQL and a combination of #Temp tables, agent jobs.
Without this SSIS will pick up that column names have changed or no longer exist and fail as a consiquence without you manually amending the ETL's
Sorry that may not be much help but as I've personally never had to overcome this challenge (yet) its all I can offer.
A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe