none
COnverting Numeric data type (Oracle) to Date Data type using SSIS RRS feed

  • Question

  • We have some columns in a table where the date is stored as 19980101 (YYYYMMDD). The data type for this column is NUMBER(8) in Oracle.

    I need to copy rows from Oracle to SQL Server using SSIS. I used the Data Conversion transformation editor to change it to DT_DATE, but the rows are not being inserted to the destination.

    On Error, If I fail the component, then the error is :

    There was an error with input column "ORDER_DATE_CONV" (1191) on input "OLE DB Destination Input" (29). The column status returned was: "Conversion failed because the data value overflowed the specified type.".

    Regards

    RH

    Wednesday, March 7, 2007 3:41 PM

Answers

  • If you are using a query to go against Oracle, you'll likely want to cast that as a varchar and then work with it in SSIS.  Not sure that YYYYMMDD will cast to DT_DATE.  You'll likely have to substring pieces of that to get it into a date.  There are plenty of examples here on this forum for doing that.  Just search for "YYYYMMDD."
    Wednesday, March 7, 2007 3:59 PM
    Moderator

All replies

  • If you are using a query to go against Oracle, you'll likely want to cast that as a varchar and then work with it in SSIS.  Not sure that YYYYMMDD will cast to DT_DATE.  You'll likely have to substring pieces of that to get it into a date.  There are plenty of examples here on this forum for doing that.  Just search for "YYYYMMDD."
    Wednesday, March 7, 2007 3:59 PM
    Moderator
  • Thanks for the reply. I used SQL on the OLEDB Source and added a TO_DATE expression in the SQL and use that column as input to my destination column.

    This worked fine.

    RH

    Wednesday, March 7, 2007 9:15 PM