Copying table data from SQL Server 2005 to SQL Server 2000 - Very Slow when using OLEDB Source a

An SSIS package to transfer data from a DB instance on SQL Server 2005 to SQL Server 2000 is extremely slow. The package uses an OLEDB Source to OLEDB Destination for data transfer which is basically one table from sql server 2005 to sql server 2000. The job takes 5 minutes to transfer about 400 rows at night when there is very little activity on the server. During the day the job almost always times out.

On SQL Server 200 instances the job ran in minutes in the old 2000 package.

Is there an alternative to this. Tranfer Objects task does not work as there is apparently a defect according to Microsoft. Please let me know if there is any other option other than using a Execute 2000 package task or using an ActiveX Script to read records from one source and to insert them into the destination source, which I am not certain how long it might take and how viable will that be?

Any inputs will be much appreciated.

Thanks,

MShah

What defect are you referring to in "Transfer Objects" task?|||

Check this out for the defect:

http://lab.msdn.microsoft.com/ProductFeedback/viewFeedback.aspx?feedbackid=336f6832-f68c-4a7f-be74-3e62e1310609

MShah

|||This bug was specific to using "SQL Server Authentication" at the destination of the transfer. It has been fixed in SP1.

So, to copy data from one table to the other, you should be able to use Transfer task with "Windows Authentication" at the destionation if you are using SQL Server 2005 RTM. If you have SP1 installed, you should be able to use "SQL Server Authentication" or "Windows Authentication".|||

Thanks. I will upgrade to SP1. We only use SQL Server Authentication here, no windows / mixed mode.

sql