SSIS cannot convert because a potential loss of da

问题:

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.

回答1:

When you first set up this package, I am guessing that either a one or two digit number was the first value in the ShipTo column. Your package reading from the Excel picked a numeric type for that input field and the word "ALL" fails the package since the input spec for that field is numeric. There are several ways to fix this beforehand, but to fix it after the fact, the easiest way is to right click the Excel Source and choose Show Advanced Editor... From there, choose the tab that says Input and Output Properties. In the topmost part of the inputs and outputs section of that dialog box, find the column ShipTo. You will have to drill down to find it. Set the DataType to "string [DT_STR]" and the length to 20.

Click OK then attempt to run your package again.

回答2:

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.

回答3:

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

回答4:

Try this one as it worked for me:

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

回答5:

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.