limit on # of transformations to Oracle?

Question

limit on # of transformations to Oracle?

Joan Shroyer-Keno

Old Hand

Points: 381
More actions
January 26, 2005 at 10:03 am

#65513

I'm trying to load some data from my server to the data warehouse that is sitting on Oracle. I can only get a certain# of colums to load. When I try to load the whole table (46 columns), the package just hangs up and won't finish executing.
I tried to split the load in half, and it works, but I am not sure how to write the update statement to Oracle database that is on another server.

Viewing 5 posts - 1 through 4 (of 4 total)

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

t.o. SSC Veteran Points: 220 More actions · Answer 1

I've loaded over 100 columns directly from SQL Server to Oracle using DTS transform task and OLE DB drivers for the connections. Only weird stuff I've had is load from source to target over the WAN/LAN terminating prematurely without an error code. In those cases the load seemed to be impacted by the total width of the rows. When I changed the Options\Data Movement\fetch buffer size to 1 the full load completes successfully. Not sure if a similar buffering problem could be manifested by the load hanging. But, it's worth a try.

Joan Shroyer-Keno Old Hand Points: 381 More actions · Answer 2

Thanks for the info. I checked the settings and the fetch buffer size is set to 1 already. Are you using the Fast Load option? What driver are you using for your ODBC conncetion? I'm using ORACLE ODBC driver.

I am successful in pulling data from the same qmart using this setup, but just can't seem to push data there.

t.o. SSC Veteran Points: 220 More actions · Answer 3

Not using ODBC. I've used both the Microsoft Oracle OLE DB driver in the most current MDAC as well as the Oracle OLE DB provider driver for Oracle 9iR2. I think the "fast load" option only applies to SQL Server targets. It is ignored for Oracle.

If you have a load that is taking too long the alternative to "fast load" is to run DTS to a falt file target. Load to Oracle using SQL Loader utility of loading using straight Oracle SQL identifying the flat file as an external table. Load will be orders of magnitude faster.

Antares686 SSC Guru Points: 125444 More actions · Answer 4

I have always found the Oralce OLE driver to give the least issues. But make sure your driver revision is comparable to the server you are attaching too. I have done in excess of 60 columns on some really unusual packages and in somecases multiple concurrent operations for different sets.