SSIS: Insert and Update data in Oracle table from SQL Server

Recently I was asked to develop a package which would pull data from SQL Server Database and insert and update data in Oracle database.

To begin with this, we have to keep few things in mind:

First make sure that you know what data type are in your source and destination table. For example say EmpID is a integer in SQL Server database but chances are that it is NUMBER in your Oracle table to make sure to do correct data conversion task.

Secondly, in SQL Server, commit is auto, so we don't have to issue any statement (chances are that database is setup like this) however in oracle database you have to issue commit statement after you make any change to data.

For my SSIS packages, I designed my task into following steps:

1. I created a temp table in oracle database to dump all my data from sql server (using right data conversion and data type.
2. I created a Merge Stored Procedure on Oracle side which I would execute inside my SSIS package to update and insert data.

And everytime, I use DML statement, I use SQL excute task to issue commit statement on Oracle Database.

Also make sure that you use ADO.Net connection Manager to communicate with your oracle database.

To call stored procedure inside Execute SQL task use something like this.

 CALL MY_Stored_Procedure ()

If you get this error message,

Warning: Multiple-step OLE DB operation generated errors. 
Check each OLE DB status value, if available. 
No work was done.

It means that you have missed commit statement.