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

need matrix report advice

I have to create a report that displays monthly actual and forecasted values by project with a variance column for the current month. Has anyone ever attempted something like this? I am having a difficult time determining how the data should return from the procedure so that it can be display in the following format:

JUL

AUG

SEP

OCT

NOV

DEC

JAN

FEB

Actuals

Var

Forecast

PROJECT A

10

20

15

-15

30

15

41

26

47

64

PROJECT B

15

10

25

5

20

20

10

5

10

10

I need to use a matrix since the number of columns can vary. I have thought about returning the following fields: project, date, label, hours.

I am hoping someone has attempted this before and can offer advice on whether I am approaching the problem correctly.

Thanks for any help.

hey there

not sure if this is what you want but this is how I approached a similar report using a Matrix - with varying columns

Report was for a Weekly user worktime

put this in your layout view

(1)=(Parameters!PersonNameValue)

(2-across)=Fields!WorkDate.Value)

(3)=Fields!CallSubject3.Value

(4-across)=Format(Sum(Fields!WorkTime.Value)

(5)=Format(Sum(Field...hrs total)

(6) blank

this shows like below

person date1 date 2 etc

subject time time etc

total time totaltime totaltime etc

sorry I wasn't sure how to post a graphical view on here so I hope this helps you

just add extra fields as required

any questions please ask

Jewel