CODEWITHSUNDEEP
sql-serverexcelssisetl
Ankit Tyagi
Ankit Tyagi

Reputation: 175

SSIS :- variable fof excel filename

i have a folder containing multiple excel file. Excel files name are almost same except every file name contain month and year number in last.

Example 

Emp_04_2017.xlsx
Emp_05_2017.xlsx
...

I want to create a SSIS package that pick the current month file and insert it into the destination table.

Upvotes: 0

Views: 437

Answers (2)

observer
observer

Reputation: 316

Use a ForEachLoopContainer.

Complete solution

Variable declared

ForEach Loop Container will pick files from the FolderPath variable and return complete CompletePath (Path+fileName). Loop will iterate through all the files in the FolderPath location.

  1. Foreach Loop Container: Double click -> In Collection set Expression Directory = FolderPath, Enumerator Configuration -> Files : (.xlsx). Vaiable Mappings -> Variable (CompletePath) Index 0.
  2. EXPR_GetFirstOcrDash: Expression used to get first occurrence of dash in filename, @[User::FirstOcr] = FINDSTRING(REVERSE(@[User::CompletePath]), "_", 1).
  3. EXPR_ExtractFileName: The expression is used to get month from the file name, @[User::FileMonth] = (REVERSE(SUBSTRING(REVERSE(@[User::CompletePath]), @[User::FirstOcr]+2, 1)) == "0" ? REVERSE(SUBSTRING(REVERSE(@[User::CompletePath]), @[User::FirstOcr]+1, 1)) :REVERSE(SUBSTRING(REVERSE(@[User::CompletePath]), @[User::FirstOcr]+1, 2)))
  4. EXPR_SetFileToProcess: Used to set the file which we found for processing, @[User::FileToProcess] = @[User::CompletePath]
  5. EXPR_StopProcessing: The loop will continously check all files in the folder, when we found first file with month of current date, we will not further look for file. A better practice could be use two directory Source and Archive, once file is processed move the processed file to Archive directory using FileSystemTask.

Precedence Constraints are added on the green arrows.

After the Foreach Loop Container gets processed, you can use FileToProcess variable and use the file in the DataFlowTask.

Upvotes: 1

ChrisIsError
ChrisIsError

Reputation: 28

One way would be to create SSIS variables to store the current month and year, and then use those to construct the name of the file in a third variable.

Upvotes: 1

Related Questions