{TOC}

SSIS DYNAMIC COUNTRY CODE COMPONENT

#

PRESERVING REFERENTIAL INTEGRITY

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.

API Reference, CLR Reference (links)

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:
This will create an extra column with the prefix “Masked_”.
6. 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. Now, all the configurations are complete for the valid values. You can run the component and see the results of masking:

Error Handling

8. 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. 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 Generic Alpha Numeric component.
The value in the original file is erroneous in spelling” Antarcticaa” vs. “Antarctica” and it results in error, in the error destination file:
Generic Alpha Numeric 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