How to use DMS transformation rules to remove leading/trailing spaces in data on the fly?

DMS offers several powerful string functions that can be incorporated into transformation rule expressions to handle space trimming automatically:

1. Removing Leading Spaces The ltrim(x,y) function eliminates all characters specified in y from the beginning of string x. When y is not specified, ltrim(x) automatically removes all leading spaces.
2. Removing Trailing Spaces The rtrim(x,y) function removes all characters specified in y from the end of string x. When used as rtrim(x) without the second parameter, it removes all trailing spaces.
3. Removing Both Leading and Trailing Spaces The trim(x,y) function eliminates all characters found in y from both the beginning and end of string x. Used as trim(x) without the second parameter, it removes all spaces from both sides.

These functions can be strategically implemented in DMS transformation rules to ensure clean data transfer between disparate database systems.

In this article, we will demonstrate how to use DMS to handle leading and trailing spaces in the data during the migration, SQL Server will be used as source and Aurora PostgreSQL as target.

SQL Server (Source) Setup

We will create a demo table, insert data into the table and then update the data to add trailing spaces for demonstration purposes.

1. Create demo table and insert data:

create table demo (id int, first_name varchar(20))
insert into demo values (1, 'Test-1')
insert into demo values (2, 'Test-2')
insert into demo values (3, 'Test-3')
insert into demo values (4, 'Test-4')
insert into demo values (5, 'Test-5')

2. Validate data in SQL Server:

select first_name LEN(first_name) from demo

3. Update data to add trailing spaces in the data for testing purposes

update demo set first_name = 'Test-1      ' where id  = 1

4. Validate the data length, to confirm trailing spaces:

DMS Setup

As we have trailing spaces in the data, let’s use DMS to remove such spaces. This needs to be done by removing the column and then adding a new column over which the transformation will be applied. Remember to have the rule-id for remove-column of a lower value than add-column as shown below:

{
  "rules": [
    {
      "rule-type": "transformation",
      "rule-id": "3",
      "rule-name": "3",
      "rule-target": "column",
      "object-locator": {
        "schema-name": "dbo",
        "table-name": "demo",
        "column-name": "first_name"
      },
      "rule-action": "remove-column",
      "value": null,
      "old-value": null
    },
    {
      "rule-type": "selection",
      "rule-id": "537301229",
      "rule-name": "537301229",
      "object-locator": {
        "schema-name": "dbo",
        "table-name": "demo"
      },
      "rule-action": "include",
      "filters": []
    },
    {
      "rule-type": "transformation",
      "rule-id": "5",
      "rule-name": "5",
      "rule-action": "add-column",
      "rule-target": "column",
      "object-locator": {
        "schema-name": "dbo",
        "table-name": "demo"
      },
      "value": "first_name1",
      "expression": "TRIM($first_name)",
      "data-type": {
        "type": "string",
        "length": 20
      }
    }
  ]
}

Run the DMS task, which will populate the table in the target

Target PostgreSQL

Once the DMS loads complete, we can verify the table data in the target to make sure data is clean.

 SELECT first_name1, length(first_name1) AS name_length FROM <database_name>.dbo.demo;