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
[quoted text, click to view] "Allan Mitchell" wrote:
> 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/donald_farmer/archive/2005/03/13/8819.aspx >
> Allan Mitchell
>
www.SQLDTS.com >
www.SQLIS.com >
www.Konesans.com >
> > 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
> >
>
>
