Detect new column in source not mapped to destination and fail in SSIS

Question

How can I cause an SSIS 2017 package to fail if a column in the source table does not have a corresponding column in the destination table, or at least a deliberate decision to not include it?

I made a table in both called test with one column, testcol. SSIS transfers the data. Now I added a testcol2 to the source, but not to the destination. The job still runs fine the way it's handled today, but I want that to fail and report an unmapped column error.

Answer 1

Update 1

After doing more research on this issue, it looks like that ValidatExternalMetadata doesn't do what you're looking for. It will only track the metadata changes occured on the selected columns.

Based on that, i don't think there is an option in SSIS to do this, you must add your custom validation to the package such as:

1. Declare a variable that contains the columns count (when you designed the package) then add an Execute SQL Task that check the current columns count (SELECT Count(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = ?). If count are not identical then throw an exception.
2. Create a reference table which contains the Source table columns and add a Execute SQL Task to check whether there are new added columns (SELECT Count(*) FROM Information_schema.Column T1 LEFT JOIN Source Columns T2 ON T1.COLUMN_NAME = T2.Column_name WHERE T2.Column_Name IS NULL) then you check if the result is > 0 then throw an exception.
3. Use an Execute SQL Task to read from the database schema auditing table:
   • SQL SERVER – SSMS: Schema Change History Report
   • SQL server schema auditing?

---

Initial Answer

You can achieve this by setting the OLEDB Source ValidatExternalMetadata property to True.

When new columns are added it should throw an exception of type.

VS_NEEDSNEWMETADATA

Be aware that this may take additional time when executing the package.

For more information, refer to:

• ValidateExternalMetadata property, what exactly does this do?
• DelayValidation property and ValidateExternalMetadata property in SSIS
• ValidateExternalMetadata property, DelayValidation property SSIS , Package Validation in SSIS , Long time to load packages due to validation in BIDS