Post reply

SSIS: handle '' as a NULL doing conversion for dates

  • July 9, 2015 at 3:54 pm

    #319654

    Hi, all

    I have simple package to basically copy table (30 columns) and convert one of them. This column need to be casted to datetime: ENT_DATE char(10) ='20150428' and this column also can have '' (just nothing which is not NULL).

    And Destination Column is ENTRY_DT datetime.

    getting error:

    Hresult: 0x80004005 Description: "Invalid character value for cast specification".

    While doing conversion with Derived Column M I go an error, I imagine I need do CASE IF '' then NULL?

    This is what I have in my Derived box:

    SUBSTRING(ENT_DATE,1,4) + "-" + SUBSTRING(ENT_DATE,5,2) + "-" + SUBSTRING(ENT_DATE,7,2)

    This is basic pack: OLE Source, Derived Column and OLE Destination.

    Or maybe there is some other box to do this in SSIS?

    Thanks much

    M

  • July 9, 2015 at 5:33 pm

    #1811473

    I finished handling that WHEN '' then NULL in Source SQL, could not find how to code this expression in derived column, is this syntax same like SSRS (VBA) ?

    how to do this in SSIS:

    CASE WHEN COL1 ='' THEN COL1 = NULL ** not sure if there is NULL in SSIS at all???

    isNothing(SSIS, SSRS) = NULL (SQL) ???

    THanks much all

    M

  • July 9, 2015 at 10:36 pm

    #1811486

    I can't remember if this is the exact syntax, but it should be something like this:

    TRIM( ENT_DATE] )=="" ? (DT_DATE)NULL(DT_DATE) : (DT_DATE)(SUBSTRING(ENT_DATE],1,4) + "-" + SUBSTRING(ENT_DATE],5,2) + "-" + SUBSTRING(ENT_DATE],7,2))

    It's using the conditional (condition?true_value:false_value) and some casts and null functions.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

  • July 10, 2015 at 12:17 am

    #1811490

    Gracias Luis

    so we can call it SSIS language... -). I though it will be something like from SSRS.

    Best

    M

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply