I find the SSIS Integration to be extremely buggy and annoying. I use this code to read in an excel file and create a table in my database. Substitute the connection string with your database connection. The program prompts for a filename and a tableName.

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using ClosedXML.Excel;

namespace ExcelToSQL
{
    class Program
    {
        
        static void Main(string[] args)
        {
            string filePath = "d:/defensepurchasingclean.xlsx";
            string tableName = "DefensePurchasingClean";
            string connectionString = "Data Source=DESKTOP-NK1V72;Initial Catalog=dbFPDS;Integrated Security=True";
            Console.Write("Enter File Name:  ");
            filePath = Console.ReadLine();
            Console.Write("Enter table name:  ");
            tableName= Console.ReadLine();

            try
            {
                // Read the Excel file
                var workbook = new XLWorkbook(filePath);
                var worksheet = workbook.Worksheet(1);

                // Get the column names and determine the maximum length of characters in each column
                var columnNames = worksheet.Row(1).CellsUsed().ToList();
                var columnLengths = new int[columnNames.Count()];

                for (int i = 0; i < columnNames.Count(); i++)
                {
                    var columnValues = worksheet.Column(i + 1).CellsUsed().Skip(1).Select(c => c.Value.ToString());
                    columnLengths[i] = columnValues.Max(c => c.Length);
                }

                // Create the table in SQL Server
                CreateTable(tableName, columnNames, columnLengths, connectionString);

                // Insert data into the table
                InsertData(tableName, worksheet, columnNames, connectionString);

                Console.WriteLine("Data imported successfully!");
            }
            catch (Exception ex)
            {
                Console.WriteLine("Error: " + ex.Message);
            }
        }

        static void CreateTable(string tableName, List<IXLCell> columnNames, int[] columnLengths, string connectionString)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                connection.Open();

                // Check if the table already exists, and truncate it if it does
                string checkTableQuery = $"IF OBJECT_ID('{tableName}', 'U') IS NOT NULL TRUNCATE TABLE {tableName}";
                SqlCommand checkTableCommand = new SqlCommand(checkTableQuery, connection);
                checkTableCommand.ExecuteNonQuery();

                // Drop the table if it exists
                string dropTableQuery = $"IF OBJECT_ID('{tableName}', 'U') IS NOT NULL DROP TABLE {tableName}";
                SqlCommand dropTableCommand = new SqlCommand(dropTableQuery, connection);
                dropTableCommand.ExecuteNonQuery();

                // Create the table
                string createTableQuery = $"CREATE TABLE {tableName} (";
                for (int i = 0; i < columnNames.Count; i++)
                {
                    string columnName = "[" + columnNames[i].Value.ToString() + "]";
                    int columnLength = columnLengths[i];
                    string dataType = $"VARCHAR({columnLength})";
                    createTableQuery += $"{columnName} {dataType}";

                    if (i < columnNames.Count - 1)
                        createTableQuery += ",";
                }
                createTableQuery += ")";
                SqlCommand createTableCommand = new SqlCommand(createTableQuery, connection);
                createTableCommand.ExecuteNonQuery();
            }
        }

        static void InsertData(string tableName, IXLWorksheet worksheet, List<IXLCell> columnNames, string connectionString)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                connection.Open();

                using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connection))
                {
                    bulkCopy.DestinationTableName = tableName;

                    // Map the column names in the spreadsheet to the table columns
                    foreach (IXLCell columnNameCell in columnNames)
                    {
                        string columnName = columnNameCell.Value.ToString();
                        bulkCopy.ColumnMappings.Add(columnName, columnName);
                    }

                    // Convert the worksheet data to a DataTable for bulk insert
                    DataTable dataTable = new DataTable();
                    foreach (IXLCell columnNameCell in columnNames)
                    {
                        string columnName = columnNameCell.Value.ToString();
                        dataTable.Columns.Add(columnName);
                    }

                    var rows = worksheet.RowsUsed().Skip(1); // Skip the header row

                    foreach (IXLRow row in rows)
                    {
                        var dataRow = dataTable.NewRow();
                        for (int i = 0; i < columnNames.Count; i++)
                        {
                            dataRow[i] = row.Cell(i + 1).Value.ToString();
                        }
                        dataTable.Rows.Add(dataRow);
                    }

                    try
                    {
                        // Perform the bulk insert
                        bulkCopy.WriteToServer(dataTable);
                        Console.WriteLine("Data imported successfully!");
                    }
                    catch (Exception ex)
                    {
                        Console.WriteLine("Error during bulk insert: " + ex.Message);
                    }
                }
            }
        }




    }

}