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.
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.
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.
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.
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.
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