0

I have a folder in which every month I will put a single excel file with name (depending on the month) like 'Jun file', 'Jul file', 'Aug file' and others. I want to move this file from folder to Sql database and form the table of the same name, that is 'Jun file'(if it is the June Month's file). I am trying to do it through SSIS by forming the variable but I am unable to do it. How should I create the variable for this varying file name.

karaode
  • 9
  • 4
  • You can create a For each loop, loop through directory (if only files in there are ones you want) it will grab the name/path, and you can capture the values into variables to use in data flow task. – Brad Sep 21 '21 at 13:20
  • https://stackoverflow.com/questions/38151342/ssis-how-to-loop-through-files-in-folder-and-get-pathfile-names-and-finally-e – Brad Sep 21 '21 at 13:20
  • 1
    If you go with the answer from Brad, the Excel connection manager supports an `ExcelFilePath` property which is easier to work with than the full ConnectionString property https://stackoverflow.com/a/21536893/181965 – billinkc Sep 21 '21 at 14:10
  • I will have a single file in the folder every time depending on the month the file's name will change. So how to make it dynamic, like what should be the variable in that case??@billinkc @Brad – karaode Sep 22 '21 at 13:24
  • 1
    You apply a mask like *.xlsx in foreach loop. Your loop can be any number of files or none. – KeithL Sep 22 '21 at 18:11
  • @KeithL if my file name changes every month and also I have to load multiple sheets whose name also changes every month, then how to create it. Currently I m using foreach loop container with ADO.Net schema rowset enumerator and putting the excel name in connection and sheet name in variable with index as 2. But the issue is my sheet name and also excel name with change next month, so what to do?? – karaode Sep 27 '21 at 04:34

0 Answers0