I have this same exact problem, and have been fighting it for three
days. Even when I remove the lines that it says are causing the
problem, when I run it again, it just picks some new lines to fail on.

Check out this thread, where someone posts how they proved that the
problem only exists when using SQL Server 2005 to import the data:

http://forums.microsoft.com/MSDN/Sho...41512&SiteID=1

I'm convinced there is some kind of bug here, and I'm about to give up
on trying to use bulk insert altogether and just write a program to
import the file myself. SQL 2000 DTS can import the same file with no
problem. It's pretty clear there's nothing wrong with the file.

FYI, in an SSIS context, this same exact thing happens when using the
Bulk Insert task and when using the Import Column in a data flow task.
I can reproduce the problem by executing a BULK insert command in a
query window, taking SSIS out of the picture altogether.

If anyone from Microsoft is reading this and is aware of this issue,
please advise. There are clearly multiple people out here fighting it.

Dan

Kiran wrote:

More information in case someone out there might be working on this:

I am seeing this truncation error problem not just with bulk insert but
also with the SSIS data flow Flat File Source. Basically, I can't open
a flat file at all and do anything with it in SQL 2K5. I've tried
feeding the Flat File Source output to different transformations and
destinations, and I can't get around these truncation errors.

My guess is SQL2K5 bulk insert and this flat file source are somehow
sharing the same underlying code, which is apparently buggy.

Hope that helps.
Dan

Hello dan (AT) developerdotstar (DOT) com,

OK so you cannot open this one file, any file, using a Data Flow component
and the Flat File Source adapter?

Can you send me the file and any options you have set on the source and/or
destination and I will look at it.


Allan

OK, time for me to eat some crow.

Long story short, turns out there was a corruption being introduced in
the flat file from a zip extraction library. I was having this problem
with multiple files, all of which had been extracted in the same way.
The corruption did not exist in another copy of the file that I had,
which is why I was so convinced that there was nothing wrong with the
file. The copy of the file that was testing with BULK INSERT directly
had the same corruption.

Allan, thanks for your willingness to help.

My apologies to the SQL team at Microsoft for being so convinced there
was a bug here. :-)

Dan