CODEWITHSUNDEEP
sql-serverssisetlderived-column
Pedro Alves
Pedro Alves

Reputation: 1054

SSIS - Derived Column - Substring/Charindex - Case when statement

I've this SQL Code:

Select [Name] = case when CHARINDEX(''.'', [Name])>0 
                            then LEFT([Name],CHARINDEX(''.'', [Name])-1)
                        else [Name] end,
        [System] = case when reverse(SUBSTRING(REVERSE( System),1,CHARINDEX('':'', REVERSE(System)))) like '':'' 
                            then (  System + ''\'')
                        else    System end

And I'm creating my SSIS workflow in order to build my ETL using SSIS. To create the transformations above I'm using a Derived Column Object. For the first statement I try this:

ISNULL(SUBSTRING([Name],1,FINDSTRING([Name],".",1) - 1)) ? [Name] : SUBSTRING([Name],1,FINDSTRING([Name],".",1) - 1)

But it gives me error...

How can I make that two transformations?

Thanks!

Upvotes: 2

Views: 2557

Answers (2)

Hadi
Hadi

Reputation: 37313

You can use Expression Task to achieve this

  • For [Name] expression you can use the following

    @[User::Name] = FINDSTRING( @[User::Name] ,".",1) == 0 ?  @[User::Name] : LEFT(SUBSTRING( @[User::Name] ,1,FINDSTRING( @[User::Name] ,".",1) ), LEN(SUBSTRING(@[User::Name] ,1,FINDSTRING( @[User::Name] ,".",1))) -1)

SUBSTRING don't allow to subtract -1 from length you can use LEFT to achieve this

  • For [System] expression you can use the following

    @[User::System] = REVERSE(SUBSTRING(REVERSE(@[User::System]),1,FINDSTRING( REVERSE(@[User::System]),":",1))) == ":"  ? @[User::System] + "\\"    : @[User::System]

Upvotes: 0

observer
observer

Reputation: 316

  1. Create two variable like Name (String) and t (Int32).
  2. Use two expression tasks

enter image description here

  1. EXPR_Set_Flag_For_Dot: @[User::t] = (FINDSTRING(@[User::Name], ".", 1) == 0 ? 0 : 1 )
  2. EXPR_Get_SubString: FINDSTRING( @[User::Name] ,".",1) == 0 ? @[User::Name] : SUBSTRING( @[User::Name] ,1,FINDSTRING( @[User::Name] ,".",1) - @[User::t] ).

Note: In your expression you are using -1 in the SubString, when there is no dot (.) in the name, the expression will throw error. FindString will give 0 so 0-1 in the outer SubString is not allowed. The first expression already checks this and assigns @[User::t] with 0 when there is no dot, due to which 0-0 will not through error.

Upvotes: 1

Related Questions