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

Question

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!

What is the error you are getting? what type of value are you getting in the Name column? — observer, May 04 '17 at 05:57

score 1 · Answer 1 · answered May 04 '17 at 13:57

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

EXPR_Set_Flag_For_Dot: @[User::t] = (FINDSTRING(@[User::Name], ".", 1) == 0 ? 0 : 1 )
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.

Top! :) The first statement is working perfectly :) For the second statment do you know how to use Reverse() function in SSIS? — Pedro Alves, May 04 '17 at 18:29
https://learn.microsoft.com/en-us/sql/integration-services/expressions/reverse-ssis-expression — Hadi, May 04 '17 at 19:24

Hadi · Accepted Answer · 2017-05-07T07:07:39.420

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]

@PedroAlves happy to help. It is a useful question. I upvoted it. Good luck — Hadi, May 07 '17 at 15:43

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

2 Answers2