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