SSIS connector to Oracle other than Attunity

Question

SSIS connector to Oracle other than Attunity

ttepparit

Old Hand

Points: 324
More actions
May 10, 2012 at 12:33 pm

#260743

Does anyone know any other SSIS connector to Oracle than Attunity?
The reason I look for the other connectors because I use SSIS connector to Oracle by Attunity in the jobs developed in SQL Server Enterprise edition but deploy them onto the Standard edition. Apparently, it does not work and got the error.
If anyone can suggest me the other connectors which can work on both Enterprise and Standard edition, I'll really appreciate your advice.
Thank you.
Rit

Viewing 9 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Koen Verbeeck SSC Guru Points: 259165 More actions Follow · Answer 1

You can use the standard OLE DB Source/Destination with the Oracle OLE DB provider.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

ttepparit Old Hand Points: 324 More actions · Answer 2

The given standard one does give poor performance when loading data into Oracle.

Koen Verbeeck SSC Guru Points: 259165 More actions Follow · Answer 3

How so? I have used it often and it loads data just fine.

How many rows do you need to load and how do you construct your package?

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

CraYon SSC Enthusiast Points: 169 More actions · Answer 4

There 're 6 million rows of data from mssql server loading to oracel 10g directly. The SSIS package running speed is around 6 - 7 hours before failed while loading data in 1-2 million rows because of out of memmory.

So I have to change standard OLE DB Destination to Attunity Oracle destination which can finished loading the same data within a hour.

But after deploy the package to production server I found that this task cannot run on standard edition. Any idea please help. Thank

Koen Verbeeck SSC Guru Points: 259165 More actions Follow · Answer 5

Did you use the fast load option in the OLE DB destination?

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

CraYon SSC Enthusiast Points: 169 More actions · Answer 6

I use data access mode as "Table name or view name variable" which config in OLE DB Destination task.

I try to set the data access mode to "OpenRowset Using FastLoad From Variable" in properties tab of OLE DB Destination task but it clear all the setting in the task.

Anyway, I just found the article on the internet that fast load option only use on MSSQL Database. Could you please confirm me that? Thank.

Koen Verbeeck SSC Guru Points: 259165 More actions Follow · Answer 7

Can you give me the link to that article?

The official documentation doesn't mention that fast load is only for SQL Server.

OLE DB Destination

But, I tested it and it seems indeed that at both the Oracle OLE DB Provider and the Microsoft OLE DB provider have issues with fast load.

I came across this thread where someone suggests using the Execute Process Task to invoke sqlldr, some sort of Oracle bulk insert:

http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/6b1fb55a-f980-4d50-90c9-1dbbf76811a1

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

CraYon SSC Enthusiast Points: 169 More actions · Answer 8

Sorry for late reply. Here is document about loading to Oracle.

http://download.microsoft.com/download/D/2/0/D20E1C5F-72EA-4505-9F26-FEF9550EFD44/Optimized%20Bulk%20Loading%20of%20Data%20into%20Oracle.docx

I cann't find any link direct to persistent bulk load information but from the forum told that it's very expensive. Could you please suggest me the freeware that can use on SSIS standard edition 2008.

Thank.

SSIS connector to Oracle other than Attunity

Cookies on SQLServerCentral