IBM i Global

 View Only
  • 1.  Converting SQL server datetime to IBM i timestamp

    Posted Thu August 05, 2021 04:35 PM
    We are using SSIS IBMDA400 OLE DB driver to insert data to db2.  The datetime to timestamp throws an error at execution.
    The db2 timestamp has a precision of 6.  These are the errors.

    [CABOTFIL TPOCHKCNL [2]] Error: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80004005.

    An OLE DB record is available.  Source: "IBMDA400 File Rowset"  Hresult: 0x80004005  Description: "CPF5029: Data mapping error on member TPOCHKCNL.

    ".

    An OLE DB record is available.  Source: "IBMDA400 File Rowset"  Hresult: 0x80004005  Description: "CPF5035: Data mapping error on member TPOCHKCNL.

    ".

    [CABOTFIL TPOCHKCNL [2]] Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR.  The "CABOTFIL TPOCHKCNL.Inputs[OLE DB Destination Input]" failed because error code 0xC020907B occurred, and the error row disposition on "CABOTFIL TPOCHKCNL.Inputs[OLE DB Destination Input]" 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_PROCESSINPUTFAILED.  The ProcessInput method on component "CABOTFIL TPOCHKCNL" (2) failed with error code 0xC0209029 while processing input "OLE DB Destination Input" (15). 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.

    How do we format the datetime so we can insert into a timestamp?

    Ken



    ------------------------------
    Ken Harding
    ------------------------------


  • 2.  RE: Converting SQL server datetime to IBM i timestamp

    Posted Thu August 26, 2021 08:49 AM
    Can you give an example of the date in and the expected date out?

    Cheers, Wim

    ------------------------------
    Wim Jongman
    ------------------------------



  • 3.  RE: Converting SQL server datetime to IBM i timestamp

    Posted Thu August 26, 2021 09:46 AM
    Hello Wim,

    The datetime is 2021-08-25 10:33:30.000 and needs to be converted to 2021-08-25-10.33.30.000000.
    I just spoke with the person working on this and they found a link that helped them.  
    It seems it was a formatting problem so he has created a function to format the datetime correctly.
    The link they said helped them is String representations of datetime values - IBM Documentation

    Thanks,
    Ken

    ------------------------------
    Ken Harding
    ------------------------------