SQL server Schema drift - Source Columns keeps changing

Question

Hi there,

I have built a simple ADF framework for ETL pipeline.

Contains a control table which has list of source tables and source table query (pic attached). Using look up activity, I loop thru the table names and truncate existing tables data at target and load/autocreate target tables use data copy activity.

select 1 'ID', 'Source1' SourceSystem, 'dbo' SourceSchema, 'Table1' SourceTableName, 'Select * from Table1' SourceQuery, 'Staging' TargetSchema, 'Table1' TargetTableName union select 2 'ID', 'Source1' SourceSystem, 'dbo' SourceSchema, 'Table2' SourceTableName, 'Select * from Table2' SourceQuery, 'Staging' TargetSchema, 'Table2' TargetTableName union select 3 'ID', 'Source1' SourceSystem, 'dbo' SourceSchema, 'Table3' SourceTableName, 'Select * from Table3' SourceQuery, 'Staging' TargetSchema, 'Table3' TargetTableName

The source systems are adding new columns and sometimes dropping columns at the SQL server source tables.

Failure happened on 'Sink' side. ErrorCode=UserErrorInvalidColumnName,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=The column credit_used is not found in target side,Source=Microsoft.DataTransfer.ClientLibrary,'

I ideally dont want to drop 100s of tables (currently im only truncating)

Is there a way or method to keep copying with schema changes without error? Source - SQL server tables to destination Azure SQL server tables even if structure changes? any examples you could refer me to?

Answer 1

Hi ,

Thanks for reaching out to Microsoft Q&A.

Solution 1:

Use Auto-Create Tables with Flexible Schema Copy

1. Enable "Auto Create" in Copy Activity
   • In your Copy Activity, under the Sink Settings, enable:
   • Auto create table: This will create missing tables automatically.
   • Allow schema drift: This will allow changes in schema.
2. Enable "Skip Incompatible Columns"
   • Under Mapping, set the Skip incompatible columns option.
   • This prevents errors due to missing or extra columns.
3. Full Load (Truncate & Load) Strategy
   • If you are truncating and reloading, the new schema will be considered automatically when reloading

Solution 2:

Use Mapping Data Flows with "Allow Schema Drift"

• Use a Mapping Data Flow instead of Copy Activity
  • Add a Source transformation and enable Schema Drift to capture all columns dynamically.
  • Use a Sink with Allow Schema Drift enabled, ensuring new columns flow without failures.

Solution 3:

Use "Stage and Merge" Strategy

Load into a Staging Table (with dynamic structure)

• Instead of loading directly, copy into a wide, flexible staging table.
  • The staging table should use a JSON or XML column for unexpected columns.

Use MERGE with Dynamic SQL

• Load data into the main table after validating the schema dynamically.

Please feel free to click the 'Upvote' (Thumbs-up) button and 'Accept as Answer'. This helps the community by allowing others with similar queries to easily find the solution.