Programming Samples

Click here to go to:



Excel VBA

Word VBA

MS Access

T-SQL

SSIS

SSRS

Power BI

Crystal Reports

SSAS

SQL Replication

C# Code

ASP .NET Code

Oracle PL/SQL

Database Diagramming


Back to Home Page


SSIS Devart Data Conversion from Oracle to SQL Server

SSIS Package to Convert Oracle Data for Transfer to SQL Server Table

Using Devart's SSIS Data Flow Components, this article will demonstrate how to connect to a Devart Oracle Data Source, use a Derived Column, use a Data Conversion Task to connect to a SQL Server 2014 table and transfer the converted data into it. The download for the Devart SSIS Data Flow Components is here: Devart SSIS Data Flow Download Page. Installation is quick and easy and there is a Free 30 day trial on the product.

Begin by adding a Data Flow component to the Control Flow tab in your SSIS Project. Click on the Data Flow tab to continue with the project.

SSIS Oracle Connection Setup

Add a New Oracle connection by Right clicking the Connection Manager and selecting DevartOracle Connection.

Devart Oracle Connection

Click on the Direct checkbox to get the additional fields for the Port and SID boxes to show up. The user in this exercise is HR.

Devart Oracle Source

SSIS SQL Server OLE DB Connection Setup

Right click on the Connection Manager and click on the New OLE DB Connection. Click New to select the database of your choice. The database used here is AdventureWorks. Click OK to close the window.

OLE DB SQL Server Connection

In SQL Server Management Studio, create a table in the instance of SQL Server database that will be used in this package. This table is named OracleEmps.

SSMS Create Table

Devart Oracle Source Configuration

Add a Devart Oracle Source to the Data Flow. Right click and select Edit. Create a query as the data source and click OK to Close.

Devart Oracle Source Editor window

Add a Derived Column to the Data Flow tab and connect the Devart Oracle Source to it.

Right click to Edit the Derived Column. To Concatenate the First_name and Last_Name field, type FIRST_NAME + " " + LAST_NAME in the Expression box. Click OK to continue.

Derived Column Window

Add a Data Cconversion task and SQL Server OLE DB Destination to the Data Flow tab and connect them.

Data Flow Tab

Right click on the OLE DB Destination and click Edit. Select the SQL Server connection previously added and select the new table created in SSMS. Click OK to close. We will return to the Destination to setup the Mappings after fixing the Data Conversion step.

SQL Server Destination Editor

Setting up the Data Conversion

Right click on the Data Conversion Transformation Editor and click Edit. In each of the boxes under Data Type, change from Unicode String [DT_WSTR} to String {DT_STR} and add the Length for each based on the Varchar lengths used in the CREATE TABLE statement in SSMS. The columns need to be converted from Unicode to Non Unicode strings to process the data into SQL Server. If they are left to the Default value of Unicode String, the task will fail.

Data Conversion Transformation Editor Window

Right click the SQL OLE DB Destination and Select Edit and click on the Mappings. Click and map the Destination SQL Server Columns to the "Copy of" columns that are set to be Converted to Non Unicode Strings (i.e., Copy of Job_Title, Copy of Region_Name, etc.)

OLE DB Destination Mappings

Run the job and verify that the data was transferred from the Oracle database through the Data Conversion task and out to SQL Server.

SSIS Run Job

Run a Query in SSMS to verify the data transfer in the OracleEmps table.

SSMS Query to Verify Data