3

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.

Hadi
  • 36,233
  • 13
  • 65
  • 124
ADataGMan
  • 449
  • 1
  • 7
  • 23
  • You can just compare number of columns or if the columns exist in your source by comparing it with your destination, then throw some error to fail if your source has more columns than your destination table – Avi Feb 23 '19 at 04:40
  • @Hadi I think you mean `ValidateExternalMetadata` property – Yahfoufi Feb 23 '19 at 11:30
  • @Aaron check my answer update – Hadi Feb 25 '19 at 23:59
  • Why ignoring the provided answer if it solved the issue or you agree with you have to upvote it or accept it, else you should leave a comment to elaborate more with the answerer – Yahfoufi Mar 11 '19 at 11:42
  • @Yahfoufi It looks like the updated answer will work, I'm just trying to figure out how to implement it since I know nothing about SSIS. – ADataGMan Mar 13 '19 at 03:45

1 Answers1

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:

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:

Hadi
  • 36,233
  • 13
  • 65
  • 124
  • 1
    I am not getting the expected behavior here - regardless of setting that property. I created a pair of simple tables `CREATE TABLE dbo.SourceTable(col1 char(1)); CREATE TABLE dbo.TargetTable(col1 char(1));` and wired them up in a data flow. Ran, it went green. Added a column `ALTER TABLE dbo.SourceTable ADD Col2 char(1);` and reran and it still ran successfully. – billinkc Feb 24 '19 at 16:17
  • @billinkc i think it only detect selected columns metadata changes not the whole table. I will do more research on that – Hadi Feb 24 '19 at 20:06