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::CurrentE
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_Shee
Example #2
W:\ProcessFiles\Labor_Shee
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.
SUBSTRING(@[User::CurrentE
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_Shee
Example #2
W:\ProcessFiles\Labor_Shee
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.
Microsoft SQL ServerMicrosoft SQL Server 2008SQL
Last Comment
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
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
Am I going to use the above in a script component? Such as the image in my data flow below?
![Data flow]()
SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
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]
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.