[ssis] SSIS cannot convert because a potential loss of data

I have an SSIS package that imports from an Excel doc to a Database table. I am getting the following errors:

[Excel Source [1]] Error: There was an error with output column "ShipTo" (47) on output
"Excel Source Output" (9). The column status returned was: "The value could not be 
converted because of a potential loss of data.".

[Excel Source [1]] Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR.  The
"output column "ShipTo" (47)" failed because error code 0xC0209072 occurred, and the
error row disposition on "output column "ShipTo" (47)" 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.

[SSIS.Pipeline] Error: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED.  The PrimeOutput method
on component "Excel Source" (1) returned error code 0xC0209029.  The component returned
a failure code when the pipeline engine called PrimeOutput(). The meaning of the
failure code is defined by the component, but the error is fatal and the pipeline 
stopped executing.  There may be error messages posted before this with more
information about the failure.

I have done reading and found articles to ensure that my datatypes match, and I have done that. In the Excel doc, there are 184 rows, and in the column that is listed in these errors (shipto) they all have the same value and that is "All". I ran into this last time I ran it, but it was only 4 rows that had "All" so I just entered those values manually, but that won't be very efficient this time.

On the destination table, the column ShipTo is a Varchar(20) so I don't see the size being a problem being that it is one of three things: 1 digit number, 2 digit number, or the word All.

Does anyone have any additional ideas that I would be able to try? Any help is greatly appreciated. Thanks to anyone that can offer any help.

For me just removed the OLE DB source from SSIS and added again. Worked!

Try this one as it worked for me:

SSIS - the value cannot be converted because of a potential loss of data

I had the same issue, multiple data type values in single column, package load only numeric values. Remains all it updated as null.

Solution

To fix this changing the excel data type is one of the solution. In Excel Copy the column data and paste in different file. Delete that column and insert new column as Text datatype and paste that copied data in new column.

Now in ssis package delete and recreate the Excel source and destination table change the column data type as varchar.

This will work.

This might not be the best method, but you can ignore the conversion error if all else fails. Mine was an issue of nulls not converting properly, so I just ignored the error and the dates came in as dates and the nulls came in as nulls, so no data quality issues--not that this would always be the case. To do this, right click on your source, click Edit, then Error Output. Go to the column that's giving you grief and under Error change it to Ignore Failure.