SSIS cannot convert because a potential loss of data

Question

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

User · Accepted Answer

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

User · Answer

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

User · Answer

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

User · Answer

Try this one as it worked for me      SSIS - the value cannot be converted because of a potential loss of data

User · Answer

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

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

Examples related to ssis