How to load Flat files to SQL Server Tables according to the Name of Flat File?

Question

There are hundreds of flat files

• for each file i need to create a table in SQL-server and enter the file contents into the table

  example :
  Flat_Files:             SQL-SERVER Table:
  FileA.txt               fileA
  FileB.txt               fileB
  FileC.txt               fileC  
  ... and so on.
  

how to achieve this in SSIS.

Ps: i am beginner to all of this.

Answer 1

Try using Dynamic SQL in a Script task. For each file(use for-each loop container) get the header and file name to create a table and then insert the data into that table using Dynamic SQL.

This should be your last option.

If all the files are .xlsx you can do this using ExecuteSQL Task.