在源中检测未映射到目标的新列并在SSIS中失败 [英] Detect new column in source not mapped to destination and fail in SSIS

问题描述

如果源表中的列在目标表中没有对应的列，或者至少有意决定不包含它，如何使SSIS 2017程序包失败?

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?

我在两个表中都创建了一张名为testcol的表. SSIS传输数据.现在，我将testcol2添加到源中，但未添加到目标中.这项工作仍然可以按今天的方式正常运行，但是我希望它失败并报告未映射的列错误.

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.

推荐答案

更新1

在对该问题进行了更多研究之后，ValidatExternalMetadata似乎没有满足您的需求. 它将仅跟踪所选列上发生的元数据更改.

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.

基于此，我认为SSIS中没有执行此操作的选项，您必须将自定义验证添加到程序包中，例如:

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. 声明一个包含列数的变量(在设计软件包时)，然后添加一个执行SQL任务，以检查当前列数(SELECT Count(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = ?).如果计数不相同，则抛出异常.
2. 创建一个包含源"表列的引用表，并添加一个执行SQL任务"以检查是否有新添加的列(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)，然后检查结果是否为> 0，然后抛出例外.
3. 使用执行SQL任务从数据库架构审核表中读取:
   • SQL SERVER – SSMS :架构更改历史记录报告
   • SQL Server模式审核?

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?

---

初始答案

您可以通过将OLEDB Source ValidatExternalMetadata属性设置为True来实现.

---

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

VS_NEEDSNEWMETADATA

请注意，执行程序包可能需要更多时间.

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

有关更多信息，请参阅:

For more information, refer to:

• ValidateExternalMetadata属性，这究竟是做什么的?
• SSIS中的DelayValidation属性和ValidateExternalMetadata属性
• ValidateExternalMetadata属性，DelayValidation属性SSIS，SSIS中的程序包验证，由于在BIDS中进行了验证，因此加载包所需的时间很长

• 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

这篇关于在源中检测未映射到目标的新列并在SSIS中失败的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！