Post reply

Conversion failed because the data value overflowed the specified type error

  • August 12, 2008 at 5:43 pm

    #123826

    I have a .net etl package that imports data from a table in Oracle into SQL Server, however the job fails and the error I am getting is as follows:

    Error: 0xC02020C5 at INC PO_LINE_LOCATIONS_ALL, Data Conversion [18465]: Data conversion failed while converting column "QUANTITY" (9832) to column "QUANTITY" (18478). The conversion returned status value 6 and status text "Conversion failed because the data value overflowed the specified type.".

    Error: 0xC0209029 at INC PO_LINE_LOCATIONS_ALL, Data Conversion [18465]: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "output column "QUANTITY" (18478)" failed because error code 0xC0209082 occurred, and the error row disposition on "output column "QUANTITY" (18478)" 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: 0xC0047022 at INC PO_LINE_LOCATIONS_ALL, DTS.Pipeline: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "Data Conversion" (18465) failed with error code 0xC0209029. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure.

    Error: 0xC0047021 at INC PO_LINE_LOCATIONS_ALL, DTS.Pipeline: SSIS Error Code DTS_E_THREADFAILED. Thread "WorkThread0" has exited with error code 0xC0209029. There may be error messages posted before this with more information on why the thread has exited.

    I checked the external and output column datatype in the Advanced Editor of the OLE DB Source and the datatype is set to double precision float for both. I used a data conversion component, but that did not help. Any inputs in resolving this will be much appreciated.

    Best regards,

    Monisha

  • August 12, 2008 at 6:00 pm

    #857096

    Try redirecting the error rows to see the value of 'Quantity', this might help you identify the problem further.

    Also what is the datatype for your SQL server destination?

  • August 12, 2008 at 6:53 pm

    #857114

    The datatype in SQL Server Destination is numeric(38, 0). How do I redirect the error rows? Any inputs will be much appreciated.

    Best regards,

    Monisha

  • August 13, 2008 at 10:39 am

    #857574

    If you are doing it programmatically then you can follow (i have never done it programmatically)

    http://msdn.microsoft.com/en-us/library/ms136009.aspx

    Otherwise, you can go the Advance Editor->Input and Output Properties(tab)->Output Columns->(select the column e.g. 'quantity') -> Set ErrorRowDisposition = RD_RedirectRow OR TruncationRowDispostion = RD_IgnoreFailure which ever is applicable to you.

    Then you can add any destination to capture these redirected rows and check what's causing the problem.

    HTH

    ~Mukti

  • August 13, 2008 at 2:24 pm

    #857714

    How do you add the destination to capture the redirected rows? Can u please let me know? I am setting the option to redirect the output on failure.

    Any inputs will be much appreciated.

    Best regards,

    Monisha

  • August 13, 2008 at 6:16 pm

    #857799

    I redirected the error output to a table on SQL server and the table after the job failed had no data. I am not certain why that happened. Any inputs on correcting that will be much appreciated.

    thanks,

    monisha

  • August 13, 2008 at 7:51 pm

    #857810

    It was my mistake i thought that you were loading data from flatfile. Please check the sql server destination data type for Quantity field. You mentioned that its Numeric (38,0), Are you sure that the scale should be 0 for the data.

    You can try to load the data in test table with higher value of scale, please check the scale for your Oracle source and see if they match.

  • August 14, 2008 at 12:39 pm

    #858311

    I examined the values in the column causing the problem. It was a 1 E 18 value that was causing the problem. Changing the datatype of the destination column from numeric to float on the end table resolved the problem.

    Just wanted to let update the post so it will be helpful for others.

    😀

    Monisha

  • April 27, 2014 at 7:27 am

    #1709035

    It works very well , thanks a lot :w00t:

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply