Loading data with umlauts from SQL server into Oracle using SSIS and OLE DB driver

Recently, I was developing a simple SSIS package. The purpose of the SSIS package was extraction of a data from MS SQL server into Oracle database. Extraction data flow was very simple. As you can see in the picture below, there is no transformation. Data flow is grabbing data and loading it into Oracle database. I used OLE DB as the driver in components.

Image 1: Data flow

Image 1: Data flow

 

I finished my SSIS package and I did not hesitate to execute it. A moment of tension… The SSIS package execution was successful as you can see below.

Image 2: Successful data flow

Image 2: Successful data flow

 

I was very happy that I was able to successfully load data with this “extremely complicated” SSIS package. So, I went to check the result in target table. Look at the picture below and you can see that the output was not that nice. Umlauts have been replaced by upside-down question marks.

Image 3: Data incorrectly loaded into destination

Image 3: Data incorrectly loaded into destination

 

I started to think about what was going on. I started with an investigation of a source table in SQL Server. The data were correct. You can see it in picture number 4. Then I enabled a data viewer. The data were correct as well (picture number 5). Column data types were correct. Otherwise, the flow would cause an error.

Image 4: Data in source table

Image 4: Data in source table

 

Image 5: Data in data viewer during extraction

Image 5: Data in data viewer during extraction

 

I looked at destination component. The OleDb destination component have set Data access mode to Table or view – fast load. I changed it into Table or view and then executed it again. A moment of tension… The SSIS package execution was successful again. As you can see in the picture below, the data are correct. They are 1:1 compare to source table. I was curious what was going on here. I used google. After a couple minutes of research, I found this article (https://social.technet.microsoft.com/wiki/contents/articles/1957.ssis-with-oracle-connectors.aspx) with the following sentence: OLE DB provider for Oracle has a high performance loader. Bulk Insert is not supported though. This has been tested in SSIS labs and is recommended by Microsoft.

Image 6: Successfully loaded data in destination

Image 6: Successfully loaded data in destination

If you would like to use a fast load in OLE DB destination component, then you have to consider if the driver supports it.

Environment setup:

  • SQL Server 2016 as source database
  • Oracle Database 12c Release 2
  • Visual Studio 2017 with SSDT
  • Oracle 12.2 drivers

Leave a Reply

Your email address will not be published. Required fields are marked *

Fill out this field
Fill out this field
Please enter a valid email address.
You need to agree with the terms to proceed

Menu