Dynamic Sheet Name in SSIS Excel Spreadsheet imports

Scenario

I had to import excel data (just one sheet) into SQL database. The excel spreadsheet had multiple sheets where I am interested in only one sheet.

Solution

a) Create a new SSIS variable named "SheetName"

b) Create a script Task just before the Excel Source Task.

c) Add the following code to the main method of the script task

string excelFile = null;
string connectionString = null;
OleDbConnection excelConnection = null;
DataTable tablesInFile = null;
int tableCount = 0;
DataRow tableInFile = null;
string currentTable = null;
int tableIndex = 0;
string[] excelTables = null;

excelFile = Dts.Variables["User::ExcelFile"].Value.ToString();

connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + excelFile + ";Extended Properties=Excel 8.0";

excelConnection = new OleDbConnection(connectionString);

excelConnection.Open();
tablesInFile = excelConnection.GetSchema("Tables");

tableCount = tablesInFile.Rows.Count;
excelTables = new string[tableCount];

foreach (DataRow tableInFile_loopVariable in tablesInFile.Rows)
{
tableInFile = tableInFile_loopVariable;
currentTable = tableInFile["TABLE_NAME"].ToString();
excelTables[tableIndex] = currentTable;
tableIndex += 1;
}
}

Dts.Variables["User::SheetName"].Value = excelTables[0];

Dts.TaskResult = (int)ScriptResults.Success;


d) In your Excel source select the "Data access mode" as "Table name or view name variable".

e) Select the "User::SheetName" variable from the "Variable Name" dropdown list.