How to import all files ( files with deffrent extension CSV, xlsx, xlsm, xls ) in directory using Foreach loop container in SSIS

Question

I'm trying to create an SSIS package to process files from a directory that contains many files with different extension and name format (CSV, XLS, XLSX, XLSM), all the files are basically excel files that contains data i want to charge in a Database, they all have the same data structure. I've tried the ForEach File loop enumerate but it charge only files with one extension type like only files with XLS extension.

Answer 1

My knowledge is limited to vba but maybe you can the change the code to suit your work.

This vba imports csv, .xlsx files etc from an import folder named "data" and adds the first sheet to master macro containing file.

    Sub cmdImportCSV()

'import multiple sheets in data folder'
Dim wb As Workbook
Dim wbSource As Workbook
Dim wsSource As Worksheet
Dim myPath As String
Dim strFilename As String


'set path to the folder that contains the worksheets to Import folder'
myPath = ThisWorkbook.Path & "\data\"

'set import destination to current workbook'
Set wb = ThisWorkbook

'the first file in the source folder'
strFilename = Dir(myPath)

'start a loop - import all files in directory'
Do Until strFilename = ""
    'set workbook source'
    Set wbSource = Workbooks.Open(Filename:=myPath & "\" & strFilename)
    'set the worksheet source to copy from'
    Set wsSource = wbSource.Worksheets(1)
    'set where the copy is going to'
    wsSource.Copy after:=wb.Worksheets(1)
    'close the current source workbook'
    wbSource.Close
    'returns the next source workbook'
    strFilename = Dir()
Loop

End Sub