Dealing with BAD Data in SSIS

Discussion in 'microsoft.public.sqlserver.dts' started by robboll, Jun 24, 2010.

  1. robboll

    robboll Guest

    I have a simple table with five columns. One of the column is a
    date.

    When I import the data into a datetime field I get the error: An OLE
    DB error has occurred. Error code: 0x80004005. Description:
    "Invalid character value for cast specification".

    As it turns out the error occurs when it hits a date with this value
    "00/00/0000"

    Can anyone suggest a method of dealing with this issue?

    Thank you!

    RBollinger
     
    robboll, Jun 24, 2010
    #1
    1. Advertisements

  2. robboll

    robboll Guest

    Anyone familiar with RegexClean? I just imported the add-in
    transformations to take care of these type of data issues.
     
    robboll, Jun 27, 2010
    #2
    1. Advertisements

  3. to solve this in SSIS
    - import as strings
    - cast to date and redirect error rows

    there are ways to fix this in SQL (the source statement) if you need better
    performance, but easier is usually better...

    hope this helps
    Gorm
     
    Gorm Braarvig, Jul 6, 2010
    #3
  4. did you use cast in the sql query ?
    Please check the data type.
    or you can give a default value (TO_DATE()) for this column.
     
    Ryan.Guan(管悦), Jul 14, 2010
    #4
    1. Advertisements

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.