Generate Masking Scripts for SQL Server

As a testdata engineer, you follow the requirement that copies of your testdata must be masked. You want to use Datamaker to generate masked BCP scripts for SQL Server. Datamaker cannot directly generate masked BCP scripts, because BCP does not support function calls in queries. Set up your project to first create views containing the masking calls, and then produce BCP scripts against the views.
tdm38
As a testdata engineer, you follow the requirement that copies of your testdata must be masked. You want to use Datamaker to generate masked BCP scripts for SQL Server. Datamaker cannot directly generate masked BCP scripts, because BCP does not support function calls in queries. Set up your project to first create views containing the masking calls, and then produce BCP scripts against the views.
Define Transformations
  1. Open Datamaker.
  2. Create a project and register SQL Server tables.
  3. Click
    Projects, Transformation Maps
    . The Transformation Maps window opens.
  4. Click the Plus-sign to open the list of Transformation Maps.
    1. Click the Plus-sign to create a Transformation Map.
    2. Name the Transformation Map in the first column.
    3. Specify
      SQL Server
      in the DBMS column.
    4. Click the Checkbox to save your changes.
  5. Select the Transformation Map that you created from the drop-down.
  6. Click a table in the left pane and select appropriate transformations for columns.
  7. Click Save.
Generate Extract Scripts
  1. Open CA Data Subset and connect.
  2. Select 
    Build MS SQL Server Scrambled Extract
    from the Database Actions dropdown.
  3. Specify the project and version, and log in to the repository.
  4. Press the
    Run
    button.
    The Database Actions window opens.
  5. Open the Extract Details tab and fill in the following fields:
    1. Define an
      Action Name
      , for example, MYACTION.
    2. Select your
      Functions Database
      .
    3. Verify that the option 
      Persist views in functions database
      is enabled. 
      Note:
      If you disable this option, the necessary temporary views are created in your production database.
    4. Enter your source and target databases.
  6. Open the Data Scrambling tab and select the Transformation Map.
  7. Click Generate.
CA Data Subset creates several sql and batch script files.
Export Data to a Dump File
To export data, run the following batch script in your export directory.
EXPORT\SQLSERVER\MYACTION\extractandload\
MYACTION
_export.bat
The batch scripts expect the following parameters:
  • If you do not have a trusted connection configured:
    MYACTION
    _export username password server database
    Note:
    The user account must have "create view/function/table" privileges for the scramble database.
  • Alternatively, if you have a trusted connection configured:
    MYACTION
    _export TRUSTED 
    server database
The script outputs dump files into the 
extractandload
 directory.
Import Data From a Dump File
To import a dump file, verify that the target table is empty, and run the following batch script in your export directory.
EXPORT\SQLSERVER\MYACTION\extractandload\
MYACTION
_import.bat
The batch scripts expect the following parameters:
  • If you do not have a trusted connection configured:
    MYACTION
    _import 
    username
     
    password
     server
    target_
    database [
    target_schema]
  • Alternatively, if you have a trusted connection configured:
    MYACTION
    _import TRUSTED server
    target_
    database [
    target_schema]
The data is imported into the target database.