I have a DTS bulk insert task that is behaving oddly. It runs and
indicates that it ran successfully; however, upon examination of the
data in query analyzer, it is clear that the data loaded fine BUT only
1194 of the 504,000 rows loaded. No error message or other messages are
received.

The bulk insert task reads from a flat file and uses an existing (and
tested with bcp) format file.

This bulk insert task is being upgraded from a bcp script. The same
format file is being used. In a separate package, I created a regular
data transformation task that loads the data correctly and all 504,000
rows are loaded.

I am at a loss to understand why the bulk insert task loads only 1194
records. If there were problems with it, I would expect it to error out
right away. I have tried changing the maximumerrors setting to 1000 from
10 with no change in results.

I am interested in opinions and comments on this situation.



*** Sent via Developersdex http://www.developersdex.com ***

First thing I would do is remove DTS from the equation. Use the BULK INSERT
statement in TSQL and see if that works. If that works then we will need to
look at why the task does not like Batch size and "only copy selected rows"

Allan


"SJM" <nospam (AT) devdex (DOT) com> wrote

Indeed, this was a very helpful suggestion. Thanks Allan.

When I tried the inserts in query analyzer, the inserts work fine. After
working with this for a few days, the problem loads are not always
repeatable; however, they only happen with the bulk insert task and not
with the TSQL bulk insert command as an execute SQL task. I am now going
to use the TSQL bulk insert command since I need to fire triggers
anyway.


*** Sent via Developersdex http://www.developersdex.com ***