How To Perform Incremental Data Migration Using SSMA

SSMA provides an ability for you to customize sql statement used during data migration. This feature can be used in the following scenarios:

1. Split migration of a large table into multiple iterations
2. Migrate data partially based on specific criteria (e.g. migrate data only for a selected department)

To customize data migration sql statement:

1. Modify project setting by navigating to Tools > Project Settings

 

2. Click on General section then click on Migration

3. Look for the setting for Extended Data Migration Options and change the value to Show

 

 The final setting should look like below:

4. Select the table to be migrated (for example: HR.EMPLOYEES)

5. Navigate to Data Migration tab on the upper right window pane

6. Select Use custom select

7. Modify the sql statement (for example: adding WHERE DEPARTMENT_ID=100) and click Apply

 

8. Right click on the table to be migrated and select Migrate Data

Note: You must convert the schema and synchronize the schema to the SQL Server before migrating your data

9. Enter credential to Oracle and SQL Server when prompted
10. Review the data migration report and check the results in the SQL Server

 You may migrate additional data by repeating Step 7 -10 and updating the sql statement  each time (for example: WHERE DEPARTMENT_ID=101). Make sure to uncheck Truncate SQL Server table option if you want to append records to the destination table

 You can use the steps above to customize your data migration. Let us know what you think of the feature above.

Comments

• Anonymous
  April 20, 2011
  Great article. Thank You very much.

• Anonymous
  April 19, 2015
  I have to see this article just before my migration transaction!!

• Anonymous
  June 23, 2016
  What a life-saver! I can't download 38 million rows to my local SQL Server over VPN that times out in 24 hours, so was sure glad to find info to how to get SSMA to use a WHERE clause!