CODEWITHSUNDEEP
excelssis
Jess8766
Jess8766

Reputation: 417

SSIS Excel file name to date?

hopefully someone can help me with this.

I have an SSIS package which is fed by an Excel file, I have created a variable which places the excel file name in a derived column (ctp_finalv_250688.xlsx)

I was wondering how I would go about extracting the "250688" part of the file name and placing that in another derived column?

Many thanks for any help

Upvotes: 0

Views: 214

Answers (2)

Sam
Sam

Reputation: 410

I hope this is static naming convention. If yes, this should work using Derived column. 

LEFT(RIGHT(@[User::varFileName],11),6)

Also, you can replace same column in order to avoid adding an additional Column.

Upvotes: 0

Prabhat G
Prabhat G

Reputation: 3029

I have written an expression to suit your need.

My variable varFileName is string having ctp_finalv_250688.xlsx as input.

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

Catch this expression in another variable like this sample :

img

Upvotes: 1

Related Questions