none
Data Conversion - NULL datetime causes failure RRS feed

  • Question

  • Hi,

     

    I have a flat file source and am bringing it into an OLE DB Destination connected to a SQL Server 2005 database.  Since I am bringing in the data via a flat file, I have a conversion step in the data flow, which uses a "Data Conversion" transformation to convert each column to the appropriate destination data type.

     

    When I run my SSIS package, if the datetime column in the flatfile is NULL, then the Data Conversion step fails.  I have my Data COnversion step set to redirect error rows to an error output.  So, I get all the rows with populated datetime values in the intended destination, and none of the rows with null datetime values (these go to the error destination.)

     

    This is a problem!  I need to make sure all valid rows fall through.  It seems very odd to me that a NULL datetime could not be converted to a datetime data type.  This makes no sense, since a datetime column can naturally hold NULL, just like any other type.  So, my question is: is this a bug???  It seems to me it is.  I can't believe this would be the desired behavior?!

     

    Microsoft: please let us know whether this is expected behavior.  If so, then what would the desired workaround be?

     

    Thanks

    Monday, July 9, 2007 10:53 PM

Answers

  • Do you have the retain nulls option checked in the Flat File Source?

    If you are treating the column as a string in your flat file source, you may need to use a Derived Column transform to handle the conversion. Use an expression like:

    Code Snippet

    ([YourDateColumn]=="")|| (ISNULL([YourDateColumn])) ? NULL(DT_DBTIMESTAMP) : (DT_DBTIMESTAMP)[YourDateColumn]

     

    Monday, July 9, 2007 11:31 PM
    Moderator

All replies

  • Do you have the retain nulls option checked in the Flat File Source?

    If you are treating the column as a string in your flat file source, you may need to use a Derived Column transform to handle the conversion. Use an expression like:

    Code Snippet

    ([YourDateColumn]=="")|| (ISNULL([YourDateColumn])) ? NULL(DT_DBTIMESTAMP) : (DT_DBTIMESTAMP)[YourDateColumn]

     

    Monday, July 9, 2007 11:31 PM
    Moderator
  • Thanks much - the checkbox in the flatfile source did the trick.  Never noticed that before, but it worked great.  Thanks again.
    Tuesday, July 10, 2007 7:02 PM
  • Hi,

    I am tying to import date values into my SQL from an Excel source. I tried the above said transformation but it didnt seem to work. What to do when the source excel file contains empty or null values in the date column in excel.

     

    Thanks in advance.

    pallikonda_s

    Monday, August 25, 2008 5:56 PM
  • Very helpful!  Thanks.

    Jeff

    Tuesday, July 8, 2014 11:07 PM