IMPORTANT FOR DATA MASKING SSIS FEATURES
The data masking in the context of SSIS is mainly a**bout Data Flow of the Data Flow task and associated toolbox components.
Control Flow elements augment the overall workflow of the lifecycle.
Sources in SSIS are components that allow to connect to a data source, be it a database, a file, or a service in order to retrieve data from the entity.
Native source components allow integration of any flat (delimited), excel, xml and native to SSIS format (what SSIS calls "raw") files.
Hadoop as a special file type is supported starting with SQL Server 2016
SSIS connect to any database that support the following drivers: ODBC, OLE DB, ADO.NET, ADO, and SQL Server Native Driver. Relational Data Management Systems with such connectivity include well known vendors ( Oracle's Oracle and MySQL databases, Microsoft's SQL Server, Access, and SQL Server in Azure, Sybase, IBM's database varieties, and not so well known such as Postgres, Ingres, SQL Lite, etc.)
You can integrate with SaaS solutions in two ways. One of them consuming service into web service task and outputting into file for further processing. Another way is to create a script component in the dataflow with the following output ( the example to follow)
In addition to masking components outlined in the wiki, the native SSIS components most used with the data masking include the following:
Derived column with its powerful expression engine is used to complement the rules of masking components.
The example would be taking just a year part out of the date element or concatenating first and last name.
Here is another example of using Derived Column to
standardize the letter case of the column.
Letter Case Standartization with Derived Column
Let's look at how we can convert the entries in the column to be either all letters with upper case or lower case. At first, we will prepare our data. We will configure a source file (Flat File Source) filled with strings in different letter case.
1.1 Open flat file source settings (two click on the icon).
1.2 Create flat file connection manager.
1.3 Select the local source file (txt).
2.4. Press "OK"
Content of source file you need to write manually with proper structure, for example:
When Source file has been configured, you need to connect it with Derived column component.
Then you need to configure the Derived Column component.
2.1. Open the derived column component settings by double clicking on it.
2.2. Enter the name of the Column.
2.3. Select "Replace" value from the drop-down "Derived Column" (an existing column will accept new data from a transformation algorithm).
2.4. Configure "Expression" column according to needed function ( Example: UPPER(FirstName) ).
To convert letters into Upper Case you need to select
"UPPER(String)" function (see in green on the screenshot).
Upper case was often used in mainframe applications, and often remain a legacy.
To convert letters into Lower Case you need to select "LOWER(String)" function (see in blue on the screenshot).
Lower case has been often used as informal standard in