0

I have multiple excels with timestamp in a folder. The format of the name of excel contains name_timestamp.xlsx.

I have similar data schema but I need to add data from excel files.

I need to automatically get the excel for today's date into SSIS and transfer it to another database.

Any idea how I can pull new excel sheet from multiple file from a folder into SSIS source?

Moulshree Suhas
  • 27
  • 10

1 Answers1

0

You'll need a variable with path to the file where name of the file will be dynamic and based off date. Expression for file name could look something like that:

"name_" + (DT_WSTR, 4)YEAR(GETDATE()) + (DT_WSTR, 2)MONTH(GETDATE()) +  RIGHT("0"+(DT_WSTR, 2)DAY(GETDATE()),2)  + ".xlsx"

result:

name_2020430.xlsx

Then in Properties>Expression in Excel Connection Manager use that variable as a source. Also you may want to set DelayValidation property of the Connection Manager to true so that it won't throw an error for non-existent file as the expressions are evaluated at runtime.

AdamL
  • 12,421
  • 5
  • 50
  • 74
  • Can you send me any documentation related to it? I tried it. It seems it's not working. The expression just won't be enough. The value is only showing me the file name and format, but it is not taking any excel sheet into the excel source. – Moulshree Suhas Apr 30 '20 at 14:14