Hi,

I'm trying to write a SSIS package to transfer data from multiple sources
into a SQL database to use in our datawarehouse. I'm having speed issues
with the Oracle data extract. I've tried using Microsoft's OLE DB provider,
Oracle's OLEDB provider and also Microsoft's improved OLE DB provider, but
they all run really slowly - around 9,000 records per 40 seconds. In DTS
(using the same machines) I can get around 500,000 records in 30 seconds...
so something isn't right somewhere.

I can create a package in DTS on 2k and it runs in around 10 minutes - If I
then run that DTS package through the migration wizard and convert into SSIS,
when it runs under SQL 2005 it takes 20 minutes.

Is there something obvious i'm missing? Can anyone help?

Thanks

--
Dave Wall

Hello Dave,

I would start by redesigning the package rather than use the wizard reengineered
one. This way you know eveything that the package does. It is a common complaint
that the extract from Oracle to SQL Server is slow.

Have a look at this article from Donald Farmer

http://www.sqljunkies.com/WebLog/don...3/13/8819.aspx

Allan Mitchell
www.SQLDTS.com
www.SQLIS.com
www.Konesans.com

Thanks Allan,

I've already read the article you refer to, and have tried re-writing the
package directly in SSIS, but the speed of the data copy is really bad. I've
checked the network utilisation between the servers and it spikes at around
2.5% every 40 seconds as it copies the data from Oracle to SQL, but there's
no other traffic going on at that time. I'm assuming i've missed something,
because there's no way this would have made it out of Microsoft's QA with
performance this bad.

At present - a package written in SSIS takes around 11 hours to copy data
which only takes 30 minutes under DTS in SQL 2000. I'm really looking for
advice as to where to look for performance issue - or if possible a best
practices book/web site I can check against.

Any futher ideas?

Dave

---
Dave Wall


"Allan Mitchell" wrote: