I am using DTS in SQL Server 7.0 (w/ servicepack1) to import data from a text file to a table. There is a large amount of data, i need to set the "insert commit size" in the "data transformation properties" to ensure the performance and also to allow me to resume if an error occurs. If the package run successfully with any error, that's fine. However, if it meets an error, i am likely to rerun the package. Rerunning the package will casue it to hang at the first process till timeout. Setting the "insert commit size" to 0 or 1, will not cause the error. Any number above 1, will trigger this error.
This is what the import package does,
1. Drop the existing table and recreate it
2. Import data from a textfile into the table
Perhaps i had used this feature wrongly, missed some setting or its an existing bug in SQL Server 7.0. Please enlightening on this issue. thanks in advance.