We are using SQL Server 2008.
We will use SSIS package to upgrade the database schema.
Current approach to upgrade the schema (with DTS package)
Ø Creating temp table
Ø Transfer the existing data with transformation task
Ø Drop the existing table
Ø Rename the temp table
Hence, new table with newly schema is available in database.
SSIS Package approach
Temp table and transferring the existing data is not required, if tables schema is same as available in SSIS package. This step can be bypassed.
Is there any way we can compare table schema in SSIS? If table definition which is present in SSIS (or any SSIS task) & current db connection will is going to upgrade is same then I want to bypass the data transfer task.
Request you to suggest the way to compare database table schema in SSIS package.
Please also suggest any alternative to achieve the same.