Paul_Harvey (2/24/2010)
For Field 3 - SUBSTRING([RefNumber],FINDSTRING("/",[RefNumber],3),(FINDSTRING("$",[RefNumber],3)) - FINDSTRING("/",[RefNumber],3))This is accepted by the Derived COlumn transformation, but when I debug the package it fails with the following:
[Derived Column [4862]] Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR.
The 3rd argument of Findstring specifies which occurrence of the 2nd argument you want. You've placed the number 3 there for backslash, but I only count 2 for backslash and also 3 for $, but I only see one.
Try:
SUBSTRING([RefNumber],FINDSTRING("/",[RefNumber],2),(FINDSTRING("$",[RefNumber],1)) - FINDSTRING("/",[RefNumber],2))
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP