Automating DQS Domain Value Import

This post has been republished via RSS; it originally appeared at: SQL Server Integration Services (SSIS) articles.

First published on MSDN on Aug 05, 2013

There are times when you may want to update a DQS Knowledge Base through an automated process. For example, you add new products in your ERP system and you would like to make sure the new product names are added to DQS Knowledge Base automatically. A free codeplex project, SSIS DQS Domain Value Import , from OH22 data enables you to automate adding values into a DQS Knowledge Base. In this first of 3 part blog series, I walkthrough an example of using this component for adding new values to DQS Knowledge Base from a flat file. Part 2 of this series describes a more complex scenario of importing values of different types (correct/invalid) as well as setting up synonyms. The last article describe the error handling. Let's start with the first scenario.

As an example, suppose that we have a DQS Knowledge Base called Colors with an empty domain called Name :

The goal is to import the values from a flat file into the Colors Name :

Step 1: Install SSIS DQS Domain Value Import Solution

1. Download SSIS DQS Domain Value Import codeplex project


2. Run Windows Installer Package (msi) on the SQL Server 2012 (with Integration Services)

Step 2: Configure SSIS project to use SSIS DQS Domain Value Import

1. Create a new Integration Services project using SQL Server Data Tools


2. On the Data Flow tab, create a new Data Flow task


3. Drag and drop Source Assistant and configure to connect to the flat file containing the color name


4. Drag and drop SSIS DQS Domain Value Import (under Common)


5. Connect the output line from the source to the SSIS DQS Domain Value Import


6. Double click SSIS DQS Domain Value Import to open configuration editor

• Specify connection to the DQS Server


• Select the Data Quality Knowledge Base from the drop down (e.g. Colors). Note: Make sure the DQS Knowledge Base has been published before you configure SSIS element. When you create a new Knowledge Base and create a domain without publishing, you may get an error message because the domain does not exist until you publish the Knowledge Base.


• Select the Domain from the drop down (e.g. Name)


• Select input column for the Leading Value . Since I use the flat file with no header, I select column 0 from the drop down.


• Accept all other default setting (In part 2 of this series, I will explain how to use those options)


• Click OK to finish

The final SSIS project looks as follows:

Execute the SSIS project (make sure the DQS Knowledge Base is published before you execute the project, otherwise, you will get a run time error). Open the Knowledge Base from DQS Client and click on Domain Values tab to review the result:

All the values from the flat file are successfully imported into the DQS Knowledge Base.

In part 2 of this series, I describe the different options supported by SSIS DQS Domain Value Import , including how to import domain values with invalid types and set synonyms. In part 3 of this series, I will explain options to handle error and how you can view the error information.