Truncation error SQL server 2005 Delete/Insert

Question

So I am trying to do a bulk insert with SSIS and continually get:

"Microsoft SQL Native Client" Hresult: 0x80004005 Description: "String or binary data would be truncated."

Even though I already have a data conversion for every column into the exact same type as the table that the rows are getting inserted into. I used a view and the data looks like it supposed to just before the DB insert step. Still get the error.

Next I went into sql server management studio and setup an insert query into that damned table and still get the same truncation error. I then did a set ANSI_WARNINGS OFF and the insert works data looks good in the table. Now when I try to delete this row I get the Truncation error.

My question besides any basic input to the situation is how can I turn off the ANSI_WARNINGS within SSIS so that the bulk load can go though?

Answer 1

Are there triggers on the table you're inserting into? Then the error may come from an action that the trigger takes.

Answer 2

It sounds like you have a column that is too narrow to accept the data you are submitting.

Can you verify if this is or isn't the case?

I had a very similar issue arise frequently while we were nailing down a schema with a third party.

Can you select a LEN of all of the columns in the view? That could help find the issue.

Other than that, the only way I have found is to print out a report of the actual lengths of the source data columns.

Answer 3

Sounds like you've got one row (possibly more, but it only takes one!) where your data value exceeds the length of the table columns. Doing a data conversion to the shorter type will MOVE the error to whatever transform does the conversion from the Destination. What I'd recommend is creating a Flat File Destination, and tying the error output of your transforms to it. Change the error result to 'Redirect Row'. This will allow all the valid rows to go through, and provide you with a copy of the row(s) that are getting truncated for you to manually handle.

Answer 4

Turns out that in SSIS you can setup the OLE DB Destination with "Data Access Mode > Table or view: Fast Mode". When I chose this setting the bulk insert went through without any warnings or errors and the data looks perfect in the database. Not sure what this change did exactly but it worked and after 16hours on one SSIS insert I'm happy with results.

Thanks for the suggestions.