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!

Solution

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]