I found what the problem was. My login had sysadmin role.

This is from BOL...

The value of DEFAULT_SCHEMA is ignored if the user is a member of the sysadmin fixed...

You read my mind!

Yes, that's definately the problem. After taking away my sysadmin role it works fine.

I had logged back into the forum to update this post with this...

You've probably picked up my response to your post on the MSDN forum but for the benefit of other readers, I have decided to stick with SQL Server 2000 and...

In that case Quinn, you're getting very good transfer rates.

Every record won't have the full number of bytes of course (varchar fields only use the bytes as needed, plus 2,...

I'm pretty sure this would work. My tests with csv files of the same size go like a rocket. However, there are logistical and political reasons why I...

Thanks Quin...

It depends on how big your rows are. For instance, if your row size is about 1/4 of mine then your transfer rate is about the same.

My row size...

Update...

I have tracked the problem down to the transfer rate. The DTS package transfers at about 700 KB/sec but the SSIS transfers at about 75 KB/sec. They both run off...

No I don't have any other solutions at this stage. I fiddled a bit with OPENROWSET but didn't persist with it because it is academic only. I'll use linked-server...

Thanks Tim...

I'm running it directly on the server.

Putting the fields individually into the query hasn't made any difference either - but thanks for the suggestion.

I'm open to any suggestions at...

Even later...

I created a linked server to the AS400 using the same ODBC DSN and can return all records in 13 minutes.

This indicates to me that something is amiss with...

After further investigation I noticed that the database was in SQL Server 2000 compatibility level. Changing it to SQL Server 2005 hasn't made any difference.

Thanks for your response.

I understand what you're saying but on my system I can't rename a database this way. Tables, stored procs, views - yes, but not...

I am currently one of the 925,000 and was starting to consider myself a parasite to SSC. This article makes me feel a bit better about that in the...

Thanks for the reply.

There is about 20 gig in about 30 tables.

I'm using DTS packages created directly from the wizard so I guess it's imported in the same order as...