Oracle to SQL Server Data Migration

Data Migration from Oracle database to SQL server database is fairly automated process. All it takes is to correctly handle mapping and that’s it!. SQL Server Migration Wizard also targets Other Data Source Providers apart from Oracle database but we are primarily focusing on Oracle as these are the two most commonly used databases. Let’s take a look on how to use SQL Server Import Export Wizard for data migration.

Step 1: Starting up the utility

• Connect to Database through SSMS and in Object Explorer pane expand database hierarchy
• Right click on database and select Task > Import data option

Import data utility

SQL server Import export wizard will open

Step 2: Choosing Source Database

• Select .NET Framework Data Provider for Oracle option in Data Source dropdown.
• In property window below Data Source dropdown, scroll down to Source option and enter data connection string.

• Enter User Id and password under Security option and click Next

Selecting Source Database Provider

When you click Next, utility will try to open connection with Oracle database at the same time

Step 3: Choosing Destination Database

• Select appropriate SQL Server Native Client Destination from dropdown
• Enter Server name
• In Authentication block, select Use SQL Server Authentication and enter Username and password
• Select database & Click Next

Selecting Destination Database Provider

• Similar to Oracle Provider Selection step, when you click Next, utility will try to open connection with Oracle database as the same point.

Step 4: Choosing Tables for Data Migration

• Select Copy Data from One or More Tables or Views (Fig. 4) option and click Next and Table Mapping screen should be displayed.

Note: In this step if multiple tables are chosen for Data Migration, Data Migration utility will start migrating data for all selected tables in PARALLEL

• On Table Mapping Screen from the Source column, select the tables from which you want to migrate the data

Note: In Source column tables are denoted with schema along with table name.

The Default schema of SQL Server is dbo (database object). If schema of selected table does not exist on SQL server, utility will create the same schema on SQL server and then will create the table in that schema.

Table Mapping Window

If we do not have same schema as Oracle, utility will try to create new schema. Any new object that doesn’t exist on destination schema will be noted by below icon on Destination column

New Object notation

Similarly if Object exist on Destination schema it will be denoted with below icon

Existing Object notation

• There are some columns which need to be ignore. Select the table from source column and click on Edit Mapping. New window will open which is called as Column Mapping. Here you can see Column mapping of source table with destination table. Type column denotes data type of data in destination column.

Column Mapping window

• Click on Destination Column field of Source column that you want to ignore and select ‘ignore’ from dropdown. Click Ok
• We can check Preview of how data will take format once migration is done by clicking Preview on Table Mapping Screen. Click Next

Step 5: Verify Column Datatype Mappings and Start Migration

• In Data Type Mapping window we can check if utility can successfully convert the Oracle data type to SQL server datatype or not.

Datatype Mapping window

As you can see in above screenshot, in Table section, red cross denotes data migration will fail as there is some datatype conversion that utility is not able to convert. More details can be found in Data type Mapping section.

As you can see, conversion from CLOB datatype from Oracle to datetime datatype from SQL Server is failing for certain column. We need to go back to previous step and change the mappings through Edit Mapping window.

For this case we are going to ignore this column i.e. it will be populated with NULL value. After setting ignore option for this column when you come back again on Datatype Mapping window, any ignored columns will be displayed as below

Ignored column mapping

• Once all column mapping as edited appropriately, no error will be displayed below On Truncation option which is displayed in above image.
• Also, there can be some run time error that might occur while data migration process such as follows:

If oracle contains two rows with values “ABCD” and “abcd” in one column (Oracle is case sensitive by default) which has Unique constraint on it, while migrating such column SQL Server will throw Unique key constraint error as it is Case Insensitive by default.

• If we want to ignore such errors, we can select ‘ignore’ option from On Error dropdown and On Trucation dropdown or sanitize such data on source only.

Note: the (global) word next to OnError & OnTruncation options states that if we have selected multiple tables for data migration, the strategy that we choose from dropdown will be applied to all the tables

• Keep On Error and On Truncation strategies to ‘FAIL’ and click Next.
• On Run Package window click Run immediately option (Ignore other options if available) and click Next > Finish or directly click Finish.
• After clicking Finish utility will start Data Migration Activity.

Successful Migration activity

• In Case of Failure, details for failure can be found by clicking Messages in Message column.

Failed Migration activity

It is important to note that this wizard do not rollback transaction in case of failure. After error rectification when re-initializing migration do note the below two options in Column Mapping window as table might have partially populated with data and can lead to KEY CONTRAINTS Failures.

1. Delete Rows in destination table
2. Append Rows in destination table

These are all the steps essential for migrating data from Oracle Source to SQL Server source. As you can see its fairly simple and automated process. The only catch is handle mapping and rectify / correct data on failures

Next time I’ll share Query tactics to manually migrating Oracle Specific Queries to SQL Server Specific Queries. There is automated way for this conversion as well but this process recreate entire queries which leads to more complex queries which can include multiple new sections. Also if your queries have multiple unions & Joins, this can add additional overhead in case of debugging the issue. Hence it is essential to know how to manually change Oracle Syntax to SQL server Syntax.

Hope this article have helped you regarding Data Migration queries. Thanks.