SSIS Transfer Task That Handles Schema Changes

Question

I'm using SSIS with SQL Server 2k5 to build a transfer task to copy all of the data from one database to another. This works quite well, except for one problem - the source database will periodically have schema changes (generally just additions like new columns) but the transfer task seems to choke if the two schemas don't match exactly. Is there some way that I can use SSIS to first bring the target DB up to date with the source DB's schema, and then do the transfer?

Answer 1

You can open the package programmatically and re-save it before executing. You can also programmatically build the package using the SSIS object model.