Post reply

Error:SSIS Error Code DTS_E_OLEDBERROR, Error: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED

  • July 25, 2017 at 2:09 pm

    #327679

    Hello,

    I am moving data from OLE DB SOURCE to OLE DB DESTINATION.

    I have 749 columns in the table A and 749 columns in table B

    1)when i am trying to use data conversion transformation such as varchar to date and  varchar to int,smallint,bigint  for some reason they are not working so i used convert and cast to work out in script. is there any reason why data conversion in not working?

    2)After using cast and convert when i am trying to execute i am getting an error saying 

    [OLE DB Source [31217]] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.
    An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80004005 Description: "The conversion of the varchar value '7325724521' overflowed an INT2 column. Use a larger integer column.".

    [SSIS.Pipeline] Error: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component "OLE DB Source" (31217) returned error code 0xC0202009. 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.

    3)i made some changes to the script and when i try to select,then error is displayed as show below.
    Msg 244, Level 16, State 2, Line 2
    The conversion of the varchar value '7325724521' overflowed an INT2 column. Use a larger integer column.
    i knew how to solve this error but i am unable to identify the column name for which i got error and i got many errors in this way.
    is there any better way to identify column names for type of above errors.

    Thanks

  • July 25, 2017 at 2:28 pm

    #1952687

    This might be useful to you, if you are on 2016.
    If it were me, I think I'd run a query on the source table to find something like
    Max(cast(SourceColumn as bigint))
    for all of the columns you are converting to INT.

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • July 25, 2017 at 2:47 pm

    #1952691

    he's casting to INT2, which is IBM's as400/DB2/Universe? smallint equivalent i think.
    the logic Phil identified is still the same though, cast to bigint INT8/ and see what is greater than the  max size you are assuming.(>255 or 65535 or 2^31-1 for example)

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Viewing 3 posts - 1 through 2 (of 2 total)

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