08.12.2018, 19:20 | #1 |
Участник
|
NAV UK Blog: Configuring SSIS DQS Domain Value Import
Источник: https://blogs.msdn.microsoft.com/dqs...-value-import/
============== In the previous blog article, I described how to use the free SSIS DQS Domain Value Import from OH22 data to automate importing values into a DQS Knowledge Base domain. In this article, I will go over the optional configuration to import invalid/error value types as well as to set synonyms. First, let's quickly recap the domain type and synonym. Domain type In DQS, you can define a domain value with different types:
Synonym When setting up domain value type to Error or Invalid, you can specify Correct To value. DQS cleansing then automatically replace the value with the Correct To (i.e. synonym). The SSIS DQS Domain Value Import can automate importing values with different types and setting up synonym above. To illustrate, I use the Knowledge Base from the previous article: Importing domain values with different types Consider that we would like to insert new values to the domain Name from a SQL table: CREATE TABLE DQSCOLORS2 ( NAME NVARCHAR(64), DomainType INT ) -- Note: that the column containing domain value must have NVARCHAR/NCHAR and Domain type must be set to INT data type. INSERT INTO DQSCOLORS2 VALUES ('white', 0) ,('tbd', 1) ,('oliver',2) The domain type column must be set to one of the following integer value:
Configure the SSIS DQS Domain Value Import as follows:
The domain values are added with the specified type. A couple of things to consider when using the Domain Type option:
In this example, I set up a new table with the following: CREATE TABLE DQSCOLORS3 ( NAME NVARCHAR(64), Synonym NVARCHAR(64) ) INSERT INTO DQSCOLORS3 VALUES ('white', 'bright white') ,('DQS_NULL', 'not specified') ,('clear', 'transparent') The first row sets synonym to an existing valid value (e.g. 'white') in the domain. The second row sets synonym to a special domain value called DQS_NULL (this value can be used to specify how to handle NULL during DQS cleansing). The last row set synonym where both the leading value and its synonym are not currently in the domain. Configure the SSIS DQS Domain Value Import as follows: Make sure the DQS Knowledge Base Colors is published, then execute the SSIS package and review the result: The synonyms are successfully added to the DQS domain. 'bright white' and 'not specified' are set as Correct to 'white' and DQS_NULL accordingly. This means, for example, that when you are cleansing records containing 'not specified', it will be corrected to null. Lastly, both 'clear' and 'transparent' are added with 'clear' set as the leading value. Note that when importing leading value and synonyms using SSIS DQS Domain Value Import, the synonyms are always set to invalid type (e.g. 'transparent') while the leading value are always set to correct (e.g. 'clear'). A few things to consider when using the Synonym option:
In this last example, we will import data that include both type and synonym: CREATE TABLE DQSCOLORS4 ( NAME NVARCHAR(64), DomainType INT, Synonym NVARCHAR(64) ) INSERT INTO DQSCOLORS4 VALUES ('red',NULL ,'dark red') ,('blue',0 ,'dark blue') ,('blue',1 ,'light blue') ,('blue',3 ,'bright blue') ,('yellow',0 , NULL) DQS Domain Value import is configured to map all three input columns: The result of the SSIS package execution shows the following: A few things to consider when using both Domain Type and Synonym option:
Источник: https://blogs.msdn.microsoft.com/dqs...-value-import/
__________________
Расскажите о новых и интересных блогах по Microsoft Dynamics, напишите личное сообщение администратору. |
|
|
|