Need suggestion on loading a 50 million records table from Oracle

All,

I need to load a 50 million records table monthly. Any suggestion about the best/fast way to do it?

Thanks a lot

All

I tried today; it used 8 hours to load the data just from the Oracle table to the staging table on sql server 2005. It is unacceptable!!! There are must be a better way to do it.

In the data flow, there is only an OLE DB source (from Oracle) -> data conversion transformation(convert the CLOB type) > OLE DB target (SQL server 2005)

Do I need to do more in the data flow? What should I do?

Many Thanks

|||

There IS a better way of doing it. Scott Barrett has done loads of work on this sort of stuff and has written some great blogs on it. Especially this one: http://microsoftdw.blogspot.com/2005/11/final-storyhow-to-get-data-out-of.html.

Search this forum as well - you'll find loads of good stuff.

Lastly, if you do only one thing make sure its to read this fabulous post (http://www.sqljunkies.com/WebLog/donald_farmer/archive/2005/03/13/8819.aspx) on SSIS and Oracle from Donald Farmer, Group Program Manager for SSIS.

-Jamie

sql