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.
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.
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.
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.
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.
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.
Add a Data Cconversion task and SQL Server OLE DB Destination to the Data Flow tab and connect them.
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.
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.
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.)
Run the job and verify that the data was transferred from the Oracle database through the Data Conversion task and out to SQL Server.
Run a Query in SSMS to verify the data transfer in the OracleEmps table.
