Learn more about SQL Server tools


Tutorials          DBA          Dev          BI          Career          Categories          Events          Whitepapers          Q&A List          Join

Transfer data from Oracle - SQL using SSIS

Asked 5/25/2010 9:59:00 PM by Manuraj vs

 I have a scenario.
I have an Oracle DB as source in whihc Employee table with 1 million records are there.
Also there is another employee details table which stores the extra details about these
1 million employees.

I already Have 10K employee records in the SQL DB (Destination DB) employees table.
The empolyee details table in SQL is empty.

Now the requirement is to develop a package which will transfer the employee details
of these 10K employees to the SQL server DB(Employee details table)
& the rest of the employees (1 million - 10K) employees to an excel file

Whats the best way to desing this scenario?

Answered 5/26/2010 2:12:04 AM by sachin (0) vote answer is useful vote answer is NOT useful

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.



Answered 5/28/2010 10:13:26 AM by admin (0) vote answer is useful vote answer is NOT useful

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.



Answered 5/31/2010 2:17:10 AM by Manuraj vs (0) vote answer is useful vote answer is NOT useful


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






Answered 6/11/2010 8:40:21 AM by GaryMazzone (0) vote answer is useful vote answer is NOT useful

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.

Post an Answer

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name   *Email   Notify for updates
*Enter Code refresh code

Sponsor Information



Get Free SQL Tips










BI Professionals


Q and A

Today's Tip







Tip Categories

Search By TipID



First Timer?




User Groups

Author of the Year

More Info








Copyright (c) 2006-2017 Edgewood Solutions, LLC All rights reserved
Some names and products listed are the registered trademarks of their respective owners.