none
How to handle error of convert oracle datetime to sql datetime?

    Question

  • Hi, All,

    I am developing small tool to convert oracle table to SQL table, which is using SSIS.

    however, a little issue is confusing me,

    In Oracle database, if there is no value filled, the value will be '0000-00-00 00:00:00', which cannot be conver into MSSQL.

    I would like to know, if there is any good methods to handle it via SSIS?

    Thanks.

    Jeffers

    Saturday, November 11, 2017 7:38 AM

All replies

  • Yes

    In SSIS you can add a derived column to convert those values to a valid datetime value in SQLServer.

    Depending on your SQLServer datatype you can choose between one of 17530101 if its datetime or 0001-01-01 if its datetime2

    The expression will look like this for ex

    (DT_DATE) ((DT_WSTR, 30) [Column] == "0000-00-00 00:00:00" ? "1900-01-01" :   (DT_WSTR,20 )  [Column])


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page


    • Edited by Visakh16MVP Saturday, November 11, 2017 8:38 AM
    Saturday, November 11, 2017 8:30 AM
  • Hi, Vasakh,

    Thanks a lot for your update.

    It seems the convert have no problem for itself. However, it does not works.

    The example as:

    Step 1, I put an Ole DB Source (For oracle), I had put an select SQL

    Step 2, I put a Derived Column

    Step 3, I put an Ole DB Destination

    I had enabled the data viewer of link between step 1 and step 2.

    I can monitor the date column error as:

    'year,month,and day parameters describe an un-representable date time'

    Do you have any ideas on it, Thanks.

    Jeffers

    Monday, November 13, 2017 12:15 AM
  • when you find all zeros insert null values.

    you can use derived columns or expressions. in your dataflow on error insert null for this field.

    Monday, November 13, 2017 12:51 AM
  • Hi Jeffers Yuan,

    Did you map the right columns to SQL Server table columns after converting the date using Derived Column Transformation?

    This reason for this error message "'year,month,and day parameters describe an un-representable date time'" is either the order of Year,Month,Day is not correct or the year is less than 1 or greater than 9999. Since you have converted them, so please check the mapping columns between Derived Column and OLE DB destination.

    Regards,

    Pirlo Zhang


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Tuesday, November 14, 2017 8:24 AM
  • Hi, Vasakh,

    Thanks a lot for your update.

    It seems the convert have no problem for itself. However, it does not works.

    The example as:

    Step 1, I put an Ole DB Source (For oracle), I had put an select SQL

    Step 2, I put a Derived Column

    Step 3, I put an Ole DB Destination

    I had enabled the data viewer of link between step 1 and step 2.

    I can monitor the date column error as:

    'year,month,and day parameters describe an un-representable date time'

    Do you have any ideas on it, Thanks.

    Jeffers

    If you've a select command on Oracle then easiest thing would be to do the conversion there using a case when statement. Then you dont have to do any conversions within SSIS


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Tuesday, November 14, 2017 9:12 AM