Transfer data from Oracle - SQL using SSIS

Hi Manuraj,

For Exporting Data from Oracle to SQL there are a lot of free tools available.

But if you want to use the SSIS feature of SQL SERVER.You have to follow some steps.

1. First you have to install Oracle N/W and connectivity utilites to the Destination Server i.e SQL SERVER Box.

2. Using SSIS you have to Establish connection using DATA FLOW TASK with ORACLE DB as Source and SQL SERVER DB as Destination.

3. You have to Put the Transformation in Between to Transder the Data between Oracle to SQL.

4. You can Specify the Query as well as the Exact table name in Transformation purpose.

This is the easiest way to Transfer data between Oracle and SQL SERVER 2005.

 Sachin

Here is a tip that is related, that shows how to move data from SQL Server to Oracle using SSIS.  You should be able to do the same in reverse.

http://www.mssqltips.com/tip.asp?tip=2011

Hi,

 My issue is not with importing data from Oracle.... the issue is with the logic for the problem.

Regards

M

I would do a selection from both in Employee tables (SQL and Oracle).  (Hopefuklly there is a key that is the same in both).

So we have a DataTask

In the Data Task we have 2 Source selections (Based on queries).

Add a Sort task below each and connect.

Add a Merge Join task below the Sorts.  (I'm assuming that Oracle is on the left side and SQL on the right here).  Connect the Oracle side to the Join assign it as the Left side.  Connect the SQL to the task.

In the Merge Join select Left Outer Join as the Join type.  Select all fields from the Oracle side to move foreward.  Select one field from the SQL side to move forward and I call this AlreadyThereId.

Add a Conditonal Split to the flow and have it connected to the Merge Join.  In the condition split we make two conditions.  Condition 1 is AlreadyThereId is NOT NULL, Contion 2 Is AlreadyThereId is NULL. Name the two conditions as 1 is named Move to SQL 2 is named Move to Excel.

Add two OLEDestinaions to the flow.  One will be to the SQL Server table.  You connect this to the ConditonalSpilt using the Move To SQL output. The second is to the Excel file connected by the Move to Excel output.

You will need 3 Source conncetion strings , 1 for Oracle, 1 for SQL Server and the last one is a file connection to the Excel spreadsheet.

<Edit> I had added a MultiCast but that is not needed here </Edit.