On the Configure Error Output window, Set the value to the selected cells to
and click OK
Double click the Flat File Destination to configure (note: prior to this step, you may want to create an empty text file on your machine)
Click New to configure the connection and select the file format on the pop-up dialog box.
Click Browse to locate to the empty flat file on your machine.
Click Mappings to review the mapping and click ok to finish
Your dataflow should look like:
Execute the project and open the resulting error file.
Let's review the error log. The first three errors are self-explanatory. Below is the additional explanation for the failure in the last three:
Reason for error
'clear', NULL, 'tbd'
Both 'clear' and 'tbd' already exists in the domain. Setting synonym when both values already exists in the domain is not supported. You will need to do this through DQS client
'dark blue', NULL, 'deep blue'
You can't set synonym to a value that is not a leading value. 'dark blue' is already set to be corrected to 'blue'. Chaining leading value from 'deep blue' to 'dark blue' to 'blue' is not supported. You must set synonym ('deep blue') directly to ultimate leading value ('blue')
'light green', NULL, 'green'
This is a tricky one. The reason for the error is because the 'light green' and 'green' are in the wrong order. 'green' should be set as leading value (the first column) while the new synonym should be set in the last column. Reversing the order may partially corrupt the domain as the 'light green' will be imported (see the screenshot below) while the synonym is not set.
Knowledge Base Publication
Ok, I admit that this option is not necessary related to error handling, but more on how you manage the integrity of the DQS Knowledge Base. When you updating the Knowledge Base, you effectively creating a new version of the Knowledge Base and it is "checked out" to you during editing. While you are working on updating the Knowledge Base, any DQS projects continue to use the (previously) published version of the Knowledge Base.
When you automate updating DQS Domain Value through SSIS, there is a possibility that the Knowledge Base is being "checked out" for editing by a Knowledge Base owner through DQS Client. SSIS DQS Domain Value Import fails when the target Knowledge Base is not published to prevent conflict. Hence, you should check to make sure the Knowledge Base is published before running SSIS. However, what happen when there is a failure in updating the Knowledge Base during SSIS execution?
The following are the available options:
Publish When There Is No Error:
By default, the SSIS DQS Domain Value Import is set to this option. If there is any error, then the Knowledge Base will remain in unpublished state to allow the Knowledge Base owner to investigate and all other DQS projects continue using the "last known good state" of the Knowledge Base (before the import failure).
: Depending on your scenario, you may tolerate the error and change to this option. For example, if your automation is a simple case of importing valid values (without synonym or different types), then you may consider to change the option to "Always Publish". Typically when importing correct value, you encounter failure due to duplicate (value already exists in the domain). However, DQS prevents duplicate values being inserted -- if you try to import duplicate values, only 1 will be inserted. The fact that there is duplicate value in the import files -- and the duplicate values 'failed' to be imported -- do not affect integrity of the Knowledge Base.
You may use this option if you want to be conservative and prevent the Knowledge Base to be used for cleansing until the Knowledge Base owner reviews the import job (regardless there is any error or not)
The default option works in most cases, but the other options provide nice flexibility when designing your process flow.
In this article, we discussed the error handling option for SSIS DQS Domain Value Import. It is a good complement to the DQS Cleansing component and enables DQS to be integrated with other system. Let us know what you think about the component.