Data Masking Components: Country Code Dynamic

{TOC}

SSIS DYNAMIC COUNTRY CODE DATA MASKING COMPONENT

PRESERVING REFERENTIAL INTEGRITY API Reference, CLR Reference (links)

Purpose

The purpose of the component is to mask an element of country code in such a way that it preserves the format, satisfies ISO, and allows for repeatable replacement value across an enterprise preserving referential integrity.

Algorithm

Preserving Referential Integrity Substitution Algorithm

Description

Component allows masking of all three types of country codes as per ISO 3166, whether they are two letters, three letters or digital code. It expects valid country codes on entrance and produces consistent mapping on exit. It decides which ISO to use – so you can have mixed country code types in the source.

Usage Instructions:

1. Configure country code source to contain a country code column. The data should include two-letter, three-letter, or three-digit country code. Country name is also a possible entry value, if confirmed by one of the ISO 3166-1.
2. Drag and Drop Country Code Component, connect the source and the country code component:
3. Now, the precedence constraint (the blue arrow) passes proper meta-data for the country code component. If you click on it, you will see:
4. Now that the metadata for the country code exists, and values are passed into the masking component, please open the component editor:
5. In the second tab, there are input columns. Please check-mark only one column, the Country Code:
6. This will create an extra column with the prefix “Masked_”.
6.7. Create a connection manager for the destination and configure source component for the destination. In it in the tab “Mappings” specify that you want newly created CountryCode_Masked to be a field replacing the original value. For that, just click on the available input columns, chose the masked value and map to the “Available Destination Columns”
7.8. Now, all the configurations are complete for the valid values. You can run the component and see the results of masking:

Error Handling

8.9. If, however, there are invalid values in the package, one would need to configure error handling. For that, each data masking component has error handling precedence constraint. One needs to create error connection and connect red arrow (error handling constraint) with this destination. As the connection is made, one needs to configure the state: “Fail”,”Ignore” or “Redirect”.
9.10. It is recommended that one re-directs the output into the error destination, so that later one be able to analyze and process data for quality purposes. After the re-direct is done, one still can mask the data with the Country Code Dynamic component.
11. The value in the original file is erroneous in spelling” Antarcticaa” vs. “Antarctica” and it results in error, in the error destination file:
12. Country Code Dynamic component will make it masked – or if one choses, one can not process it at all. The rest of the components are configured in a similar fashion.

Download Example