SSIS: Read Excel Tables

Define two variables; ExcelFile –> String and ExcelTables –> Object. Drop a script task on designer surface;

Here is the script;

public void Main()
{
            string excelFile;
            string connectionString;
            OleDbConnection excelConnection;
            DataTable tablesInFile;
            int tableCount = 0;
            string currentTable;
            int tableIndex = 0;

            //string[] excelTables = new string[5];
            string[] excelTables = new string[20];

            excelFile = Dts.Variables["ExcelFile"].Value.ToString();
            connectionString = $"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={excelFile};Extended Properties=Excel 12.0";
            excelConnection = new OleDbConnection(connectionString);
            excelConnection.Open();
            tablesInFile = excelConnection.GetSchema("Tables");
            tableCount = tablesInFile.Rows.Count;

            foreach (DataRow tableInFile in tablesInFile.Rows)
            {
                currentTable = tableInFile["TABLE_NAME"].ToString();
                char lastCharacter = currentTable[currentTable.Length - 2];
                if (lastCharacter == '$')
                {
                    excelTables[tableIndex] = currentTable;
                    tableIndex += 1;
                }
            }

            Dts.Variables["ExcelTables"].Value = excelTables;

            Dts.TaskResult = (int)ScriptResults.Success;
}

You can display object variables values using this;

string[] tablesInFile = (string[])Dts.Variables["ExcelTables"].Value;
            foreach (string tableInFile in tablesInFile)
            {
                results += " " + tableInFile + EOL;
            }

            MessageBox.Show(results, "Results", MessageBoxButtons.OK, MessageBoxIcon.Information);

Add to favorites