Post reply

SSIS Expression in the Derived Column Transformation Editor

  • July 18, 2018 at 11:35 am

    #358442

    Trying to create an SSIS Expression in the Derived Column Transformation Editor that will add a leading zero to day and month of text values like this
    3/3/2015   =   03/03/2015
    only add zero to day value of these
    12/4/2015  =  12/04/2015
    only add zero to month value of these
    1/11/2015  =  01/11/2015

    testing with Excel I created this
    =IF(ISBLANK(F19),"",IF(FIND("/",F19,1)=2,IF(FIND("/",F19,3)=4,REPLACE(REPLACE(F19,1,0,0),4,0,0),REPLACE(F19,1,0,0)),IF(FIND("/",F19,4)=5,REPLACE(F19,4,0,0),F19)))
    So I thought I would be able to adapt that to an SSIS Expression replacing FIND with FINDSTRING and Changing the syntax for REPLACE, but Expression syntax for REPLACE does not use a location and I'm running out of time...

     This was my attempt at converting that to an Expression  -- IT FAILS --
    (DT_WSTR,50)(LEN(TRIM(NOTE_DT)) == 0 ? NULL(DT_WSTR,1) : TRIM(FINDSTRING(NOTE_DT,"/",1)==2 ? FINDSTRING(NOTE_DT,"/",3)==4 ? FINDSTRING(NOTE_DT,"/",3)==4 : REPLACE(NOTE_DT,1,0,0)) : FINDSTRING(NOTE_DT,"/",4)==5 ? REPLACE(NOTE_DT,4,0,0) : NOTE_DT ))

    Can someone please help.
    Thank you ..

    Also I thought I had learned a secret way to use an expression editor in that "Derived Column Transform Editor" (years ago) but I'll be darned I've forgotten....  Do you know how to do that?

  • July 18, 2018 at 11:51 am

    #1998066

    Something like this:

    right("0" + TOKEN( @[User::TextDate] , "/",1 ),2) + "/" + right("0"+TOKEN( @[User::TextDate] , "/",2 ),2) + "/" + TOKEN( @[User::TextDate] , "/",3 )

    Replace @[User::TextDate] with your date variable.

    The absence of evidence is not evidence of absence.
    Martin Rees

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • July 18, 2018 at 3:09 pm

    #1998109

    Phil Parkin - Wednesday, July 18, 2018 11:51 AM

    Something like this:

    right("0" + TOKEN( @[User::TextDate] , "/",1 ),2) + "/" + right("0"+TOKEN( @[User::TextDate] , "/",2 ),2) + "/" + TOKEN( @[User::TextDate] , "/",3 )

    Replace @[User::TextDate] with your date variable.

    AWESOME !!  THANK YOU Phil !!
    At first, I thought I was going to need to build conditional statements around it, BUT NO... 🙂
    That worked perfectly, and I've added it to my tips list.

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

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