Error: The field is too small to accept the amount of data.

[Excel Destination [2]] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x00040EDA. An OLE DB record is available.
Source: “Microsoft JET Database Engine” Hresult: 0x80040E21 Description:
“The field is too small to accept the amount of data you attempted to add. Try inserting or pasting less data.”.

OR

[Excel Destination [12]] Error: There was an error with Excel Destination.Inputs[Excel Destination Input] .Columns[Copy of Project SharePoint Site] on Excel Destination.Inputs[Excel Destination Input]. The column status returned was: “The value violated the schema’s constraint for the column.”.

Cause: During data load from OLE DB source my one column has varchar (4000) which is causing problems during insertion in Excel Destination.

Solution

First option:

I used the first row as header in the template excel during excel creation. I put the space characters in the header to make its length greater than 255 which is describe length of excel cell. If I put 300 space as column header then it will fixed my issue. Note: You can replace the excel template with excel with file system task.

Suppose I have column 2 which header name is “Product Name” then convert it as “Product Name ” by adding space into it to make its length around 300 then it will fix the issue.

Second Option:

Create EXCEL template and add one or two rows having values greater than 255 in Excel column which causing issue and it will help to run the package without error but at last use the script task to delete these rows.

Script Task: Delete rows from an Excel File using SSIS Script Task