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); } } } } } }