Data Migration failure from Oracle to SQL using SQL Server Migration Assistant tool

  • Article

Hello all,

Recently, I worked on an interesting case for data migration from Oracle to SQL Server. I got the error message below at the time of migration.  

PROBLEM DESCRIPTION

Getting the error, "The given value of type String from the data source cannot be converted to type bit of the specified target column", during data synchronization at the time of migration of Oracle Source Data to SQL Server using the SQL Server Migration Assistant tool. This occurs while trying to convert the Data Type from an Oracle number (1, 0) into a SQL Server bit column.

To reproduce and address this issue, I thought of writing this blog. Before we look at how to reproduce and address the issue, let’s look at what Microsoft SQL Server Migration Assistant (SSMA) does.  

 

About SSMA Tool

SSMA for Oracle is a tool for migrating Oracle databases to Microsoft SQL Server 2005, Microsoft SQL Server 2008, Microsoft SQL Server 2012 or Microsoft SQL Server 2014. SSMA for Oracle:

  1. Converts Oracle database objects to SQL Server database objects;
  2. Creates those objects in SQL Server; and
  3. Migrates data from Oracle to SQL Server.

Steps to reproduce the issue

The issue in question is reproducible. I successfully reproduced the issue on the SSMA tool v5.3. Here are the steps I performed to reproduce the issue:

1.  Installed Oracle 11g server.

2.  Created the following schema on Oracle DB.

CREATE TABLE BPMUSER.KMG(

F1 NUMBER(1, 0), F2 NUMBER(2, 0));

3.  Inserted a couple of rows like you see below. 

  

4.  Installed SQL Server Migration Assistance tool from: https://www.microsoft.com/en-in/download/details.aspx?id=42655.

5.  Created a new project in SSMA tool.

 6.  Connected to the Oracle source:

 

7.  Connected to the SQL Server instance.


 
 8.  Clicked on Convert Schema.This option creates the SQL schema. To change the Data Type mapping, click on Edit:

 
  

9.  Changed the Source and Destination Type mapping.

 10.  Synchronized with database.

11.  Confirmed that the Schema is synchronized between the Source and the Destination.

 

12.  Clicked on Migrate Data.

Migration failed with the error below:

"The given value of type String from the data source cannot be converted to type bit of the specified target column"

 

 

CAUSE

Here are the steps I performed to determine the cause of the issue:

1.  Clicked on Tools in SSMA Tool --> Project Settings.

2.  Selected the General Tab --> Migration.

3.  Made sure that Extended Data Migration options was set to “Show” as follows:

 4.  Once the above setting was set, SSMA tool displayed another Tab, “Data Migration”.

NOTE: The script in data migration tab looked like this:

 I noticed that in the following query, the tool was trying to cast the Number Data to Varchar first and then to Bit. And the data conversion was failing here:

SELECT

CAST("F1" AS VARCHAR2(100)) AS" F1",CAST("F2" AS VARCHAR2(100)) AS "F2"

FROM BPMUSER.KMG t;

 

WORKAROUND

Click on “Use custom select” and change the TSQL code as follows:

SELECT F1,F2

FROM BPMUSER.KMG  t;

 

After applying the changes, I was able to successfully migrate the data. I hope you save a lot of time by using the above procedure.

 

Please share your feedback, questions and/or suggestions.

 

Thanks,

Don Castelino | SQL Server Support Team | CSS - Microsoft

 

Disclaimer: All posts are provided AS IS with no warranties, and confer no rights. Additionally, views expressed here are my own and not those of my employer, Microsoft.