is this ssis data flow transformation correct?

Question

I'm trying to extract the substring trackingCode to the point of the delimiter ~~. So for example:

www.test.com~~34235645463544563554

I wish to return:

www.test.com

i am using:

SUBSTRING(trackingCode,1,FINDSTRING(trackingCode,"~~",1) - 1)

But it is not working and I'm getting an error:

Error: 0xC0049067 at aw import from file, Derived Column [1562]: An error occurred while evaluating the function.

any ideas? if I replace the FINDSTRING with a numeric then it works.

Answer 1

Are you sure every line has a trailing number? If not, something like this could help:

SUBSTRING(trackingCode+"~~",1,FINDSTRING(trackingCode,"~~",1) - 1)

Answer 2

Your data is probably not conforming to your expectations.

Trust but verify.

I would avoid a derived column transform for anything but trivial transforms on data which you know (through direct testing) will always conform to expectations. The formulas are difficult to read in the single-line format in derive column xforms and you get very little control and ability to try different parsing options and few failure/recovery modes.

I would recommend a script task for this one. You can route rows which don't comply to a different path and use a data viewer to see what's going on with them.