CODEWITHSUNDEEP
ssisderived
AlanD
AlanD

Reputation: 5

splitting a filename variable to 2 variables in ssis

I'm a bit of a newbie to SSIS package creation. I am trying to take a variable @[User::varFileName] and split it on an underscore and insert the values into a derived column, eg.

@[User::varFileName] = chasehaddon_nov13

The derived columns would be 

list = chasehaddon
datebounced = nov13

Currently I'm trying it on using 

list = SUBSTRING(@[User::varFileName],1,FINDSTRING(@[User::varFileName],"_",1)-1)
datebounced = SUBSTRING(@[User::varFileName],0,FINDSTRING(@[User::varFileName],"_",1)-1)

Hope that sort of makes sense

Upvotes: 0

Views: 861

Answers (2)

Jayvee
Jayvee

Reputation: 10875

If you have the 2012 version you can do as follows:

list=TOKEN([User::varFileName]"_",1)                    
datebounced=TOKEN([User::varFileName]"_",2)

Upvotes: 1

Ocaso Protal
Ocaso Protal

Reputation: 20235

The first variable (list) looks fine to me, for datebounced you should try this:

SUBSTRING(@[User::varFileName],FINDSTRING(@[User::varFileName],"_",1) + 1,LEN(@[User::varFileName]) - FINDSTRING(@[User::varFileName],"_",1) + 1)

See SUBSTRING: The second parameter is the position and the third is the length of the wanted substring.

Upvotes: 0

Related Questions