hi,
I have a package that imports data from Informix 9.X to SQL Server 2000.
I found the transformation always stop at the last DTSRun OnProgress for a
long long time.
Eg. there are 534330 rows in Informix DB and the transformation processes
very quickly, but always stays at the last DTSRun OnProgress .....
ProgressCount = 534000 more than 20 minutes and then go to DTSRun OnFinish.
I check both DB status and could't found any infomation what they do at the
last DTSRun OnProgress.
Dose Informix hang or SQL Server ???

My box is 4 Xeon CPU , 8 GB Ram , SQL Server 6GB Ram.
Win2003 enterprise edition TC , SQL SEVER 2000 SP3, MDAC 2.8 and using
Infromix ODBC (CSDK 2.81 TC1) .

Any help is very appreciated.

What you may find happening is that SQL Server is adjusting indexes
whilst inserting the rows. Remember that by default SQL Server will
either commit 534330 rows or it will commit none. You should see your
transaction increasing in size as well. If your data file is not sized
to handle the increase in size due to the rows then you may also be
seeing growth of the file which will slow things down a lot.



"steffi" <steffibev (AT) hotmail (DOT) com> wrote

Allan,
Thanks for the detail.
Another question, is there a way to speed up the index adjustment?
And what's parameter I should adjust?

No way to really speed up index adjustment just make sure you either
have very few or you remove the indexes prior to load and add them back
again afterwards.

Allan

"steffi" <steffibev (AT) hotmail (DOT) com> wrote