The value could not be converted because of a potential loss of data

Bobby P 221 Reputation points
2022-10-17T18:12:11.82+00:00

We have an Excel spreadsheet that we are trying to pump into a SQL Server Table.

The error seems to be occurring on our data column [CarrierInvoicedData]

Error: 0xC020901C at Data Flow Task - Data Pump To ProShip_Reconciliation Table, Excel Source - ProShip Reconciliation Spreadsheet [48]: There was an error with Excel Source - ProShip Reconciliation Spreadsheet.Outputs[Excel Source Output].Columns[CarrierInvoicedDate] on Excel Source - ProShip Reconciliation Spreadsheet.Outputs[Excel Source Output]. The column status returned was: "The value could not be converted because of a potential loss of data.".
Error: 0xC0209029 at Data Flow Task - Data Pump To ProShip_Reconciliation Table, Excel Source - ProShip Reconciliation Spreadsheet [48]: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "Excel Source - ProShip Reconciliation Spreadsheet.Outputs[Excel Source Output].Columns[CarrierInvoicedDate]" failed because error code 0xC0209072 occurred, and the error row disposition on "Excel Source - ProShip Reconciliation Spreadsheet.Outputs[Excel Source Output].Columns[CarrierInvoicedDate]" 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: 0xC0047038 at Data Flow Task - Data Pump To ProShip_Reconciliation Table, SSIS.Pipeline: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on Excel Source - ProShip Reconciliation Spreadsheet 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.

The data column [CarrierInvoicedDate] has a "General" format in the Excel spreadsheet.

We have tried to include [CarrierInvoicedDate] into our Data Conversion step converting it to DT_DBDATE...not sure what the difference is between DT_DBDATE and DT_DATE...soooo we actually tried converting both ways and it still failed.

Now there are some rows on the Excel spreadsheet where the [CarrierInvoicedDate] is NOT valued at all.

The Table we are attempting to pump the data to has [CarrierInvoicedDate] defined as DATE type with NULL allowed.

Can anyone shed any light onto this and help us figure out what we need to do in order to get the [CarrierInvoicedDate] to pump its data to the SQL Server Table?

Thanks for your review and am hopeful for a reply.

SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,467 questions
0 comments No comments
{count} votes

1 answer

Sort by: Most helpful
  1. ZoeHui-MSFT 33,626 Reputation points
    2022-10-18T02:02:23.763+00:00

    Hi @Bobby P ,

    As you said that [CarrierInvoicedDate] has a "General" format in the Excel spreadsheet.

    Based on my test, it will format like below.

    251383-untitled.png

    If the data is like 20221018, it could not directly convert to date, you need to use Derived Column to convert the column.

    (DT_DATE)((SUBSTRING((DT_STR,50,1252)([date]),1,4) + "-"+ SUBSTRING((DT_STR,50,1252)([date]),5,2) + "-"+ SUBSTRING((DT_STR,50,1252)([date]),7,2)))  
    

    Please have a double check.

    Regards,

    Zoe Hui


    If the answer is helpful, please click "Accept Answer" and upvote it.