Error In SSIS on insert - String or binary data would be truncated

0
votes

I have an error in my ssis pakage that I don't understand it:

Error: 0xC0202009 at InsertStudent, InsertStudent [303]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E57. An OLE DB record is available. Source: "Microsoft SQL Server Native Client 11.0" Hresult: 0x80040E57 Description: "The statement has been terminated.". An OLE DB record is available. Source: "Microsoft SQL Server Native Client 11.0" Hresult: 0x80040E57 Description: "String or binary data would be truncated.".

Error: 0xC0209029 at InsertStudent, InsertStudent [303]: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "InsertStudent.Inputs[OLE DB Command Input]" failed because error code 0xC020906E occurred, and the error row disposition on "InsertStudent.Inputs[OLE DB Command Input]" specifies failure on error. An error occurred on the specified object of the specified component. There may be error messages posted before this with more information about the failure.

Error: 0xC0047022 at InsertStudent, SSIS.Pipeline: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "InsertStudent" (303) failed with error code 0xC0209029 while processing input "OLE DB Command Input" (308). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure.

Error: 0xC0047022 at InsertStudent, SSIS.Pipeline: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "Merge Join" (406) failed with error code 0xC0047020 while processing input "Merge Join Left Input" (411). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure.

2
sql-serverssisoledbetl
the third line is the one that you want to focus on. Typically this error is straightforward. Either you source or destination column widths are too small for the data you are trying to import. This error can also mean that you have a character that does not exist in the code page and can not be converted ie. off page or foreign character like accents or a tilde. Those would be a good place to start. - SFrejofsky

2 Answers

1
votes

One of your is getting an input value that is larger (in size) than what the column size is, as defined in your database table.

It's unfortunate that the error message does not also give you the name of the offending column. I think you are stuck with troubleshooting this the hard way, by looking at the values column by column until Microsoft fixes the error message

See Microsoft Connect: Please fix the "String or binary data would be truncated" message to give the column name

...and all the commentary on

Why isn't “String or Binary data would be truncated” a more descriptive error?

0
votes

Check the component connected to OLE DB Command transformation and the left input of MERGE JOIN transformation, the size of the input string from that component is larger than the column size and therefore data truncation error happened here.

The solution would be to fix the data length from that component or to change the target table structure (enlarge the column size).