Retrieve text after last back slash using SSIS Derived Column transformation

Question

Using SQL Server 2014.

I have a field that contains a string that contains a full file path i.e.

\\Server\Folder1\Folder2\Folder3\File21.csv

I only want what is after the last backslash i.e.

File21.csv

So in the world of SQL I would use:

Select RIGHT([FileName],charindex('\',reverse([FileName]),1)-1) as FileNameNew from mytable

However, how do I do this in a Derived Column in SSIS? There is no CHARINDEX so you have to use FINDSTRING. This is my expression:

RIGHT( [FileName] , FINDSTRING('\', REVERSE( [FileName] ) ,1)  -1)

But it is not working, it keeps saying the single quotation mark was not expected. I've also tried double quotes to no avail.

I don't have an SSDT install handy to test this on, but the backslash in SSIS expressions usually needs to be escaped with, of course, a backslash. So you _might_ get somewhere using `FINDSTRING('\\'...`. Worth a try, anyway. — Eric Brandt, Feb 19 '20 at 15:19
@EricBrandt it accepted that but then the package fails with: [Derived Column [2]] Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "Derived Column" failed because error code 0xC0049067 occurred — Michael, Feb 19 '20 at 15:26
New errors are progress! I'm out, though, lacking an editor to play with. — Eric Brandt, Feb 19 '20 at 15:49
haha - think I'll just revert to a SQL update after the import task. Was just trying to contain it all in the DFT — Michael, Feb 19 '20 at 15:51
@EricBrandt simplest solution is to use `TOKEN()` and `TOKENCOUNT()`. Since they are developed to solve similar cases. — Hadi, Mar 10 '20 at 21:08

score 2 · Accepted Answer · answered Feb 19 '20 at 17:58

I think you have your parameters backwards. FINDSTRING() wants the thing you're searching first, then the thing you're searching for. And you will need double quotes and an escaped backslash. This should work:

RIGHT( [FileName] , FINDSTRING(REVERSE( [FileName] ), "\\" ,1)  -1)

score 2 · Answer 2 · answered Mar 10 '20 at 19:43

Even if this can be done using RIGHT() or SUBSTRING() functions. I prefer using TOKEN and TOKENCOUNT() function to do that:

TOKEN([File Name],"\\",TOKENCOUNT([File Name],"\\"))

Example:

TOKEN("\\\\Server\\Folder1\\Folder2\\Folder3\\File21.csv","\\",TOKENCOUNT("\\\\Server\\Folder1\\Folder2\\Folder3\\File21.csv","\\"))

Result:

File21.csv

Retrieve text after last back slash using SSIS Derived Column transformation

2 Answers2