asked on

SQL String with DTS, using split string

I have a derived Column transformation in my DTS package that gets a Job number using an expression:

SUBSTRING(@[User::CurrentExcelPath],51,12) where (@[User::CurrentExcelPath] contains the filepath.

My job numbers have changed where length will not work.

I need to change to a split string.
My End goal is to strip out the job numbers from a given path. The below is 2 examples of a path

Example #1
W:\ProcessFiles\Labor_Sheet_Importation_123\ JOBNO_2014-01-01014_11331_URI- Daily Labor-Culpepper Recon 1-2-14.xlsm
Example #2
W:\ProcessFiles\Labor_Sheet_Importation_123\ JOBNO_2014-01-0001-RECON_11331_URI- Daily Labor-Culpepper Recon 1-3-14.xlsm

end result for Job Number will be:

"2014-01-0104"
"2014-01-0001-RECON"

How can I change this to strip out the job Number using a split string and will
derived column transformation Expression accept a split string?

The number of underscores will always be the same, 3, but the lenght may vary from 12 to 18 now.

BKennedy2008

ASKER

It appears I cannot use a derived column to pull this out, I guess I would need a script to pull this out in my data flow

ASKER CERTIFIED SOLUTION

Brian Crowe

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

BKennedy2008

ASKER

Am I going to use the above in a script component? Such as the image in my data flow below?

SOLUTION

Sharath S

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

BKennedy2008

ASKER

I think the script component is the path I would like to take, just need to figure out how to assign the input and output of the variables, the input being the @[User::CurrentExcelPath]

BKennedy2008

ASKER

Thanks for the help. I used a Script Component and used split function and set a variable to the JobNO Part. More coding than I wanted, but got the trick done.