Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SSIS Package SQL to Oracle Expand / Collapse
Author
Message
Posted Monday, February 3, 2014 12:14 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, February 13, 2014 1:36 PM
Points: 18, Visits: 41
Hello,

I am importing data from SQL server into Oracle Database and it's taking too much time. I tried both Native OLD DB and Oracle DB but non of them really help me to fast the data transfer.

Interestingly when I transfer data from Oracle into SQL it is running like a supersonic plan and super fast but not the other side.

Any suggestion or trick to make it fast?

Thanks,
Post #1537462
Posted Monday, February 3, 2014 3:00 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:02 PM
Points: 13,334, Visits: 10,201
The problem is probably that you're not doing a bulk insert into Oracle (while you are doing a bulk insert into SQL Server, hence the performance difference).
You can try the Attunity adaptors, they are free.

2012 version




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1537525
Posted Monday, February 3, 2014 3:51 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, February 13, 2014 1:36 PM
Points: 18, Visits: 41
Thanks for the reply. Where can I find the Adapter for Oracle?

Thanks
Yash
Post #1537536
Posted Monday, February 3, 2014 11:51 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:02 PM
Points: 13,334, Visits: 10,201
yashuvp (2/3/2014)
Thanks for the reply. Where can I find the Adapter for Oracle?

Thanks
Yash


Did you not see the link I posted?




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1537621
Posted Tuesday, February 4, 2014 7:30 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, February 13, 2014 1:36 PM
Points: 18, Visits: 41
Sorry, I just overlooked the link. I was under that impression that it's your signature!

Thanks for quick reply.

Thanks,
Yash
Post #1537762
Posted Monday, March 3, 2014 3:15 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 2:57 PM
Points: 1,194, Visits: 2,211
yashuvp (2/3/2014)
Hello,

I am importing data from SQL server into Oracle Database and it's taking too much time. I tried both Native OLD DB and Oracle DB but non of them really help me to fast the data transfer.

Interestingly when I transfer data from Oracle into SQL it is running like a supersonic plan and super fast but not the other side.

Any suggestion or trick to make it fast?

Thanks,


Did you try fast load option ?

--
SQLBuddy
Post #1547109
Posted Tuesday, March 4, 2014 12:01 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:02 PM
Points: 13,334, Visits: 10,201
sqlbuddy123 (3/3/2014)
yashuvp (2/3/2014)
Hello,

I am importing data from SQL server into Oracle Database and it's taking too much time. I tried both Native OLD DB and Oracle DB but non of them really help me to fast the data transfer.

Interestingly when I transfer data from Oracle into SQL it is running like a supersonic plan and super fast but not the other side.

Any suggestion or trick to make it fast?

Thanks,


Did you try fast load option ?

--
SQLBuddy



The fast load option is not available when Oracle is the destination.




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1547165
Posted Tuesday, March 4, 2014 8:59 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 2:57 PM
Points: 1,194, Visits: 2,211
Thought fast load was available for Oracle destination too. May be I missed it ..

--
SQLBuddy
Post #1547392
Posted Tuesday, March 4, 2014 12:48 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:02 PM
Points: 13,334, Visits: 10,201
sqlbuddy123 (3/4/2014)
Thought fast load was available for Oracle destination too. May be I missed it ..

--
SQLBuddy


It depends on the OLE DB provider I think.
It's possible the Microsoft OLE DB provider for Oracle (which is deprecated if I remember correctly) has a fast load option, but the Oracle OLE DB provider doesn't.




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1547503
Posted Tuesday, March 4, 2014 1:24 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 2:57 PM
Points: 1,194, Visits: 2,211

It depends on the OLE DB provider I think.
It's possible the Microsoft OLE DB provider for Oracle (which is deprecated if I remember correctly) has a fast load option, but the Oracle OLE DB provider doesn't.


Yeah that's what I remembered . May be Yashuvp can check that .

Here is the link ..

http://msdn.microsoft.com/en-us/library/ms141237(v=sql.100).aspx

--
SQLBuddy
Post #1547531
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse