How to repeat multi-step schema change (ETL schema changes?)

Question

I'm new to DBA and not much of a SQL person, so be gentle please.

I'd like to restructure a database that requires adding new columns, tables, and relationships followed by removing old tables, columns, and relationships. A three step process seems to be in order.

1. Change schema to add new stuff
2. Run SSIS to hook up new data using some of the old data.
3. Change schema to drop old stuff.

I'm using a SQL database Project in VS 2015 to maintain the schema, and using schema compare to update the DB schema. I'd like to make it repeatable or automatic, if possible, so I can test it out on a non-production database to get the flow right: change schema->run ETL->change schema. Is there a way to apply schema changes from within ETL or does this require manual operations? Is there a way to store two schemas into files and then apply them, other than VS publish or compare?

Answer 1

There is a SQL TASK that allows you to do what you want to do. You want to alter table (to add columns), move the data from old columns to new columns, then drop the old columns.

1) Alter table tableA add column ..
2) update table tableA set ..
3) alter table tableA drop column...

Please test your code carefully before running it.

Answer 2

It worked! Here is the example of the ETL. Note that it's important to set DelayValidation to true for the data flows and to disable ValidateExternalMetadata for some of the operations within the data flows because the database is not static.