Logic,

  1. Create table by reading header from the file(s) and by appending some audit columns if table name (as ‘file name’) not already exists.

  2. Insert file data into the table

SSIS Package Development,

  1. Create below variables in new package

    FilePath 		- String
    FileName		- String
    FileType		- String
    CreateTblScript	- String
    

    ssis1

  2. Add Script Task to the package, open and select below values

    ScriptLanguage		:	Microsoft Visual basic 2017
    EntryPoint			:	Main
    ReadOnlyVariables	:	User::FileName,User::FilePath,User::FileType
    ReadWriteVariables 	:	User::CreateTblScript
    

    ssis2

  3. Edit Script Task, and copy the below logic to editor

#Region "Imports"
Imports System
Imports System.IO
Imports System.Collections.Generic
Imports System.Data
Imports System.Data.OleDb
Imports System.Data.SqlClient
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports Microsoft.VisualBasic.FileIO
#End Region

'ScriptMain is the entry point class of the script.  Do not change the name, attributes, or parent of this class.
<Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute()> _
<System.CLSCompliantAttribute(False)> _
Partial Public Class ScriptMain
    Inherits Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase

'Global variables declaration
    Dim sFilePath As String, sFileName As String, sFileType As String, sFileFullPath As String, ErrorDetailStmt As String

    Public Sub Main()
        Try

            sFilePath = Dts.Variables("User::FilePath").Value.ToString
            sFileName = Dts.Variables("User::FileName").Value.ToString
            sFileType = Dts.Variables("User::FileType").Value.ToString
            sFileFullPath = sFilePath & "\" & sFileName & "." & sFileType

            Dim sFolder As New DirectoryInfo(sFilePath)
            Dim sFiles As FileInfo() = sFolder.GetFiles()
            Dim sFile As FileInfo
            Dim aFolder As String = sFilePath & "_" & Now.ToString("yyyyMMdd")

            If Not Directory.Exists(aFolder) Then
                Directory.CreateDirectory(aFolder)
            End If


            'Variables for Destination table details
            Dim TableSchema As String = "dbo"
            Dim ColAliasList As String = ""
            Dim ColAlias As New List(Of Dictionary(Of String, String))()
            Dim DropCreateTable As String = "Yes"
            Dim comma As String = ""
            Dim colList As String = "", col As String = ""
            Dim mapTable As String = ""
            Dim mapTableList As String = ""
            Dim CharTrim As String() = {"~", "`", "!", "@", "#", "$", "%", "^", "&", "*", "(", ")", "{", "}", "|", "[", "]", ":", ";", """", "'", "<", ">", "?", "\", "/", ".", " "}
            Dim FinalSQLStatement As String = ""

            'Variables for Time Capture
            Dim pTime As New Stopwatch

            pTime.Reset()
            pTime.Start()

            Dim ProcessConfigName As String = "", ProcessConfigValue As String = "", SourceConfigFileName As String = "", isConfig As Boolean = False
            Dim IgnoreColumnNameTrimming As String = ""

            For Each sFile In sFiles
                Try
                    sFileFullPath = sFile.FullName
                    sFileName = sFile.Name
                    sFileType = sFile.Extension
                    sFileName = Replace(sFileName, sFileType, "")

                    If sFileType = ".xlsx" Or sFileType = ".xls" Then
                        Dim connectionString As String
                        Dim excelConnection As OleDbConnection
                        Dim tablesInFile As DataTable
                        Dim tableCount As Integer = 0
                        Dim tableInFile As DataRow
                        Dim currentTable As String = ""
                        Dim tableIndex As Integer = 0
                        Dim excelTables As String()
                        Dim dtCommand As OleDbCommand
                        Dim dtAdapter As OleDbDataAdapter
                        Dim dt As DataTable
                        Dim objCol As Object

                        connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;" & "Data Source=" & sFileFullPath & ";Extended Properties=""Excel 12.0;HDR=YES;IMEX=1"";"

                        excelConnection = New OleDbConnection(connectionString)
                        excelConnection.Open()

                        tablesInFile = excelConnection.GetSchema("Tables")
                        tableCount = tablesInFile.Rows.Count
                        ReDim excelTables(tableCount - 1)

                        colList = ""

                        For Each tableInFile In tablesInFile.Rows
                            currentTable = tableInFile.Item("TABLE_NAME").ToString
                            If Not currentTable.Contains("_xlnm#_FilterDatabase") Then
                                excelTables(tableIndex) = currentTable
                                tableIndex += 1

                                mapTable = Replace(Replace(sFileName & "_" & currentTable, Chr(10), ""), Chr(13), "")

                                Dim str As String
                                For Each str In CharTrim
                                    mapTable = Replace(mapTable, str, IIf(str = "\" Or str = "/" Or str = ".", "_", "").ToString)
                                Next str

                                Dim LoadDate As Date = Now()

                                dtCommand = New OleDbCommand("select *, '" + sFileName + "' as sFileName, '" + LoadDate.ToString() + "' as CreatedOn from [" + currentTable + "]", excelConnection)
                                dtAdapter = New OleDbDataAdapter(dtCommand)
                                dt = New DataTable()
                                dtAdapter.FillSchema(dt, SchemaType.Mapped)
                                dtAdapter.Fill(dt)

                                Dim dbConn As SqlConnection = DirectCast(Dts.Connections("ADONetConnection").AcquireConnection(Dts.Transaction), SqlClient.SqlConnection)
                                Dim bc As SqlBulkCopy = New SqlBulkCopy(dbConn, SqlBulkCopyOptions.TableLock, Nothing)

                                bc.DestinationTableName = TableSchema & ".[" & mapTable & "]"
                                bc.BatchSize = dt.Rows.Count

                                comma = ""
                                FinalSQLStatement = ""
                                FinalSQLStatement += "Begin Try Exec('Create Schema " & TableSchema & "')" & Chr(10) & " End Try Begin Catch End Catch " & Chr(10) & " Begin Try Create Table " & TableSchema & ".[" & mapTable & "]("
                                mapTableList += TableSchema & "." & mapTable & ","
                                colList += "||" & TableSchema & "." & mapTable & "("

                                For Each objCol In dt.Columns

                                    col = Replace(Replace(objCol.ToString(), Chr(10), ""), Chr(13), "")

                                    If IgnoreColumnNameTrimming <> "IgnoreColumnNameTrimming" Then
                                        For Each str In CharTrim
                                            col = Replace(col, str, IIf(str = "\" Or str = "/" Or str = ".", "_", "").ToString)
                                        Next str
                                    End If

                                    For Each value As Dictionary(Of String, String) In ColAlias
                                        col = Replace(col, value("Col"), value("Alias"))
                                    Next

                                    dt.Columns(objCol.ToString()).ColumnName = col
                                    bc.ColumnMappings.Add("[" & col & "]", "[" & col & "]")

                                    FinalSQLStatement += comma & "[" & col & "]" & dt.Columns(objCol.ToString()).DataType.ToString() & dt.Columns(objCol.ToString()).MaxLength

                                    FinalSQLStatement = Replace(Replace(Replace(Replace(Replace(FinalSQLStatement, "System.String255", " nVarchar(200)"), "System.String536870910", "nVarchar(max)"), "System.Double-1", " float"), "System.Decimal-1", "Numeric(10,4)"), "System.DateTime-1", "DateTime")
                                    colList += comma & "[" & col & "]"
                                    comma = ","
                                    col = ""

                                Next
                                FinalSQLStatement += ") " & Chr(10)
                                colList += ")"

                                FinalSQLStatement += " Select 'Table Created' as SQLResult "
                                FinalSQLStatement += " End Try Begin Catch Select 'Table Exists' as SQLResult End Catch "

                                Dts.Variables("User::CreateTblScript").Value = FinalSQLStatement

                                ErrorDetailStmt = "Insert into nxg.ErrorLog(ErrorStmt) Select '" + Replace(FinalSQLStatement, "'", "''") + "' as ErrorStmt"


                                Dim ADOSqlCmd As New SqlCommand(FinalSQLStatement, dbConn)
                                ADOSqlCmd.ExecuteNonQuery()

                                bc.WriteToServer(dt)
                                'rCount += "||" & mapTable & "([" & dt.Rows.Count.ToString() & "])"

                                bc.Close()
                                dbConn.Close()

                            End If 'Ignore List
                        Next
                        excelConnection.Close()
                    End If

                    If sFileType = ".csv" Or sFileType = ".tsv" Then

                        'Variables for user defined data types
                        Dim intDT As String() = {"id"}
                        Dim dateDT As String() = {"created_at", "created_on", "updated_at", "updated_on"}
                        Dim charDT25 As String() = {"status"}
                        Dim charDTmax As String() = {"reason", "note", "desc", "comment", "detail", "progress", "embedded", "chain", "custom", "sql", "query", "caption", "prefs", "metadata"}

                        Dim aDelim As String

                        If sFileType = ".csv" Then
                            aDelim = ","
                        Else
                            aDelim = vbTab
                        End If

                        Dim objCol As Object
                        Dim Dt As DataTable = New DataTable()
                        Dim parser As TextFieldParser = New TextFieldParser(sFileFullPath)

                        parser.Delimiters = New String() {aDelim}
                        parser.HasFieldsEnclosedInQuotes = True
                        parser.TrimWhiteSpace = True

                        Dim fields As String() = parser.ReadFields()
                        Dim field As String

                        For Each field In fields
                            Dim dc As DataColumn = New DataColumn(field, field.GetType())
                            dc.AllowDBNull = True
                            Dt.Columns.Add(dc)
                        Next

                        'CreateOn 
                        Dim DCCreateOn As DataColumn = New DataColumn("CreatedOn", GetType(Date))
                        Dt.Columns.Add(DCCreateOn)
                        Dt.Columns("CreatedOn").DefaultValue = Now()

                        'Source File Name column with Default Value
                        Dim DCFileName As DataColumn = New DataColumn("sFileName", GetType(String))
                        Dt.Columns.Add(DCFileName)
                        Dt.Columns("sFileName").DefaultValue = sFileName

                        While Not parser.EndOfData

                            Try
                                fields = parser.ReadFields()
                                Dt.Rows.Add(fields)

                                'For Each field In fields
                                'If field = "" Then
                                ' field = vbNullString
                                'End If
                                'DtTable.Rows.Add(field)
                                'Next

                            Catch ex As MalformedLineException
                                MsgBox("Line " & ex.Message & "is not valid and will be skipped.")
                            End Try

                        End While
                        parser.Close()

                        mapTable = Replace(Replace(sFileName, Chr(10), ""), Chr(13), "")

                        Dim str As String
                        For Each str In CharTrim
                            mapTable = Replace(mapTable, str, IIf(str = "\" Or str = "/" Or str = ".", "_", "").ToString)
                        Next str

                        comma = ""
                        FinalSQLStatement = " Begin Try "
                        FinalSQLStatement += " Exec('Create Schema " & TableSchema & "')" & Chr(10) & " End Try Begin Catch End Catch " & Chr(10) & " Begin Try Create Table " & TableSchema & ".[" & mapTable & "]("
                        mapTableList += TableSchema & "." & mapTable & ","
                        colList += "||" & TableSchema & "." & mapTable & "("

                        For Each objCol In Dt.Columns

                            col = Replace(Replace(objCol.ToString(), Chr(10), ""), Chr(13), "")

                            If IgnoreColumnNameTrimming <> "Yes" Then
                                For Each str In CharTrim
                                    col = Replace(col, str, IIf(str = "\" Or str = "/" Or str = ".", "_", "").ToString)
                                Next str
                            End If

                            For Each value As Dictionary(Of String, String) In ColAlias
                                col = Replace(col, value("Col"), value("Alias"))
                            Next

                            Dt.Columns(objCol.ToString()).ColumnName = col

                            'FinalSQLStatement += comma & "[" & col & "]" & Dt.Columns(objCol.ToString()).DataType.ToString() & Dt.Columns(objCol.ToString()).MaxLength

                            If Array.IndexOf(intDT, col.ToLower) >= 0 Then
                                FinalSQLStatement += comma & "[" & col & "]" & " bigint "
                            ElseIf Array.IndexOf(dateDT, col.ToLower) >= 0 Then
                                FinalSQLStatement += comma & "[" & col & "]" & " varchar(50) "
                            ElseIf Array.IndexOf(charDT25, col.ToLower) >= 0 Then
                                FinalSQLStatement += comma & "[" & col & "]" & " varchar(50) "
                            ElseIf Array.Find(charDTmax, Function(x) ((col.ToLower).Contains(x))) <> "" Then
                                FinalSQLStatement += comma & "[" & col & "]" & " varchar(max) "
                            Else
                                FinalSQLStatement += comma & "[" & col & "]" & " varchar(255) "
                            End If

                            colList += comma & "[" & col & "]"
                            comma = ","
                            col = ""

                        Next

                        FinalSQLStatement += ") Select 'Table Created' as SQLResult "
                        FinalSQLStatement += " End Try Begin Catch Select 'Table Exists' as SQLResult End Catch "


                        Dim dbConn As SqlConnection = DirectCast(Dts.Connections("ADONetConnection").AcquireConnection(Dts.Transaction), SqlClient.SqlConnection)
                        Dim ADOSqlCmd As New SqlCommand(FinalSQLStatement, dbConn)
                        ADOSqlCmd.ExecuteNonQuery()

                        Dim bc As SqlBulkCopy = New SqlBulkCopy(dbConn, SqlBulkCopyOptions.TableLock, Nothing)

                        bc.DestinationTableName = TableSchema & ".[" & mapTable & "]"
                        bc.BatchSize = Dt.Rows.Count

                        For Each objCol In Dt.Columns
                            bc.ColumnMappings.Add(objCol.ToString, objCol.ToString)
                        Next

                        bc.WriteToServer(Dt)
                        'Dts.Variables("User::RowCount").Value = CLng(Dt.Rows.Count)

                        dbConn.Close()
                        bc.Close()

                    End If

                    File.Copy(sFileFullPath, aFolder & "\" & sFileName & sFileType, True)
                    File.Delete(sFileFullPath)

                Catch ex As Exception
                    Dts.Events.FireError(18, "Process Values", ex.Message, "", 0)
                    Dts.TaskResult = ScriptResults.Failure
                End Try

            Next sFile

            pTime.Stop()

            'pTime.Elapsed.TotalMinutes.ToString()

        Catch ex As Exception
            Dts.Events.FireError(18, "Process Values", ex.Message, "", 0)

            Dim dbConn As SqlConnection = DirectCast(Dts.Connections("ADONetConnection").AcquireConnection(Dts.Transaction), SqlClient.SqlConnection)
            Dim ADOSqlCmd As New SqlCommand(ErrorDetailStmt, dbConn)
            ADOSqlCmd.ExecuteNonQuery()
            dbConn.Close()

            Dts.TaskResult = ScriptResults.Failure

        End Try

        Dts.TaskResult = ScriptResults.Success

    End Sub

#Region "ScriptResults declaration"
    'This enum provides a convenient shorthand within the scope of this class for setting the
    'result of the script.

    'This code was generated automatically.
    Enum ScriptResults
        Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success
        Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
    End Enum

#End Region

End Class