Learn more about SQL Server tools



solving sql server problems for millions of dbas and developers since 2006 join MSSQLTips for free SQL Server tips
















   Got a SQL tip?
            We want to know!



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.

 

 Sachin


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.

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

 


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

Hi,

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

 

Regards

M

 

 


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
Comments
*Enter Code refresh code


 
Sponsor Information






Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | free t-shirt | user groups | community | today's tip | first timer?
Some names and products listed are the registered trademarks of their respective owners.