SSIS: Oracle to SQL Server

Steps for Oracle to SQL package:

Our requirement is to load data from Oracle to SQL with fresh data.

Prerequisites: 

Oracle provider: follow this link to install 

https://www.sqldose.com/2019/06/link-server-sql-to-oracle.html?m=1

Following are steps for inserting data from Oracle Database table to SQL Server Database table:

To do so, please drag and drop the data flow task and Execute SQL Task from the toolbox to the control flow.

Take connection of SQL Server and Insert truncate sql table in SQL Statement.

Double click on data flow task it will open the data flow tab. Now, Drag and drop OLEDB Source, OLEDB Destination and Data Conversion from SSIS toolbox to Data Flow tab and connect them.

Right click on the OLE DB Source and click Edit. Select the SQL Server connection previously added and select the table created in Oracle Database. 

Connection:

Click on Preview to check data is readable.

Click on columns and choose specified and Click on OK to close. 

Setting up the Data Conversion

Destination SQL Database have nvarchar datatype and Source Oracle have nvarchar2 data type.

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 on the OLE DB Destination and click Edit. Select the SQL Server connection previously added and select the table created in SSMS. Click OK to close. 

Click on the OLEDB Destination to setup the Mappings after fixing the above Data Conversion step.

Build project and run.

😊Enjoy