e
q
u
e
s
t
a
d
e
m
o
Masking With A Foreign Key
Introduction
A foreign key is a column or group of columns in a table that links two tables. Key masking limits the masking output to certain characters of the same seed value as the foreign key to retain referential integrity. When a foreign key is represented by multiple columns, each column should be masked separately.
Algorithms
Description
The Mask a Key component of SSIS masks unique integer and unique identifiers (GUIDs) with consistent unique values of the same type. They are mainly used to retain referential integrity between primary and foreign keys. They do not work with small integers (below 4 bytes). Key masking ensures that masked values are consistent across multiple databases within an organization, provided the databases contain the specific value being masked.
Instructions
Open Visual Studio and select an existing project or create a new project.
Drag and drop the Data Flow SSIS component from the SSIS toolbox on the left-hand side to create a data flow.
Select your data source (the table containing the foreign key) and drag and drop it into the data flow.
Create a new connection manager with your data source. Select a server name or log into a new server. Test the connection before continuing.
Select a table for masking and preview the data. In this example, we will mask the first column that contains the foreign key.
Select the Mask A Key component from the SSIS toolbox on the left-hand side and drag and drop it into the data flow.
Connect your source and masking component using the blue and red arrows.
Drag and drop the destination where the masked data will be stored from the SSIS toolbox on the left-hand side.
Connect the masking component and destination using the blue and red arrows.
Create a new table for the masked data or choose an existing table.
Click on the Mask A Key component and select an input column for masking. In this case we will select our foreign** key column.
Double click on your destination and select Mappings. Map the input column with the destination column. Click Okay to continue.
Save the project and click Start to run the project.
