SSIS Derived Column - expression to retrieve date from Filename

user_7331303 0
Viewed 1203

I have a file name "Asia Marine Workbook - Oct 2017.xlsx" coming through every month. I need a create two columns with Derived Columns 'month' and 'year' that should output as integers like 09 and 2017 from the filename..

Any suggestions or thoughts would be great! Thanks in advance!

etl ssis expression sql-server
2 Answers

The following expressions will get you what you're looking for.

YEAR

String:

SUBSTRING([ColumnName], 28, 4)

Integer:

(DT_I4) SUBSTRING([ColumnName], 28, 4)

MONTH

String:

RIGHT("0" + (DT_WSTR, 2) MONTH((DT_DATE) ("1 " + SUBSTRING([ColumnName], 24, 3) + " 2017")), 2)

Integer:

MONTH((DT_DATE) ("1 " + SUBSTRING([ColumnName], 24, 3) + " 2017"))

Note: "09" is not an integer, it would have to be a string.

user_53145300

Assuming that @[User::FilePath] is a variable that contains the following value Asia Marine Workbook - Oct 2017.xlsx

1. To get the Year value you can use the following expression

(DT_I4)LEFT(RIGHT(@[User::FilePath],9),4)

Result

2017

2. To get the month String value you can use the following

LEFT(RIGHT(@[User::FilePath],13),3)

Result

Oct

3. To get the month in Integer value

(DT_I4)(UPPER(LEFT(RIGHT(@[User::FilePath],13),3)) == "JAN" ? "01" :
UPPER(LEFT(RIGHT(@[User::FilePath],13),3)) == "FEB" ? "02" : 
UPPER(LEFT(RIGHT(@[User::FilePath],13),3)) == "MAR" ? "03" :
UPPER(LEFT(RIGHT(@[User::FilePath],13),3)) == "APR" ? "04" :
UPPER(LEFT(RIGHT(@[User::FilePath],13),3)) == "MAY" ? "05" : 
UPPER(LEFT(RIGHT(@[User::FilePath],13),3)) == "JUN" ? "06" :
UPPER(LEFT(RIGHT(@[User::FilePath],13),3)) == "JUL" ? "07" :
UPPER(LEFT(RIGHT(@[User::FilePath],13),3)) == "AUG" ? "08" :
UPPER(LEFT(RIGHT(@[User::FilePath],13),3)) == "SEP" ? "09" : 
UPPER(LEFT(RIGHT(@[User::FilePath],13),3)) == "OCT" ? "10" : 
UPPER(LEFT(RIGHT(@[User::FilePath],13),3)) == "NOV" ? "11" : 
UPPER(LEFT(RIGHT(@[User::FilePath],13),3)) == "DEC"? "12":"")

Result

10

Or convert concatenate the date with a day value and convert it to (DT_DATE) then extract the month value

MONTH((DT_DATE)"1 " + LEFT(RIGHT(@[User::FilePath],13),8))

Result

10

Also if you need to return it as string with 2 digits (i.e 01)

RIGHT("0" + (DT_WSTR,10)MONTH((DT_DATE)"1 " + LEFT(RIGHT(@[User::FilePath],13),8)),2)

Result

10

user_70312300
Related