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

Question

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

It seems like you are trying to build the exact filename, even thought you never know what the `040117` part will be? What you need to do is use the For Each File loop and use a wildcard that will pick up any file that matches this wildcard: `XYZDailySettleTransaction_20220314_*.xlsx`. In fact I suggest you don't hard limit your ingestion logic to be todays date. What happens if your process doesn't run for one or teo days? — Nick.Mc, Mar 14 '22 at 12:37
@Nick.McDermaid I Tired using * it is just printing it and not taking it as a value apart from this.This package which I have made is picking .xlsx file from a particular folder based on today's Date it picks up the files and insert into Sql table there are 10 lakhs records the first run happened perfect all data was inserted but the package did not stop it again picked up the same file could u please let me know how do I stop the Loop to stop also can u tell me how to send email once the data is inserted as well as if the file name is incorrect it should send email stating the Error — Adnan Wadekar, Mar 14 '22 at 15:05
You should think about processing all the files in a folder with a for each loop so you dont need to know the names and moving them to a different folder when processed. This way, as Nick says above, if your package doesnt run for a day or two it can catch up when it does run. — Dave Pile, Mar 14 '22 at 19:23
The correct and pretty much standard aprroach is to just process everything in a "waiting" folder, and then move the file to a "imported" or "processed" folder. This pattern is so standard and ironclad that it is documented in many places online, for example https://www.sentryone.com/blog/how-to-loop-through-files-in-a-specified-folder-load-one-by-one-and-move-to-archive-folder-using-ssis — Nick.Mc, Mar 15 '22 at 00:43

score 1 · Answer 1 · answered Mar 14 '22 at 19:07

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.

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

1 Answers1