How to get part of a Excel file name in SSIS Expression

user_13580187 0
Viewed 166

I have an SSIS Package that picks up only current date files from a particular folder the name of the Excel files changes dynamically

Filename: XYZDailySettleTransaction_20220314_040117.xlsx

I am able to get XYZDailySettleTransaction_20220314.xlsx as my Output

Below is my Expression:

"XYZDailySettleTransaction_" + 
(DT_WSTR, 4) YEAR(GETDATE()) + 
RIGHT("0" + (DT_WSTR, 2) MONTH(GETDATE()),2) + 
RIGHT("0" + (DT_WSTR, 2) DATEPART("DD", DATEADD("day",0,GETDATE())),2) + 
"_" +".xlsx"

Kindly help to get 040117 which is dynamic

What I exactly want is the expression should directly consider anything after GETDATE() and before .xlsx

etl ssis getdate excel expression
1 Answers

I suggest using the following expression in the FileSpec property of a ForEach Loop container to get all files that start with the current date prefix:

"XYZDailySettleTransaction_" + 
(DT_WSTR, 4) YEAR(GETDATE()) + 
RIGHT("0" + (DT_WSTR, 2) MONTH(GETDATE()),2) + 
RIGHT("0" + (DT_WSTR, 2) DATEPART("DD", DATEADD("day",0,GETDATE())),2) + 
"_*.xlsx"

In the ForEach loop container, select the ForEach file enumerator option, and within the expression form, assign the above expression into the FileSpec property as shown in the images below.

enter image description here

enter image description here

user_70312300
Related