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.

The connection "{XXX}" is not found. This error is thrown by Connections collection when the specific connection element is not found.

Scenario

I had a few connections created in the Connection Managers in SSIS and all these connection configurations are stored in the Package Configurations. I just deleted a few unwanted connections from the Connection Managers and I got the following error with the package for every single deleted connection.

Error loading XXX.dtsx: The connection "{XXX}" is not found. This error is thrown by Connections collection when the specific connection element is not found.

Solution

Update your package configurations to reflect the changes made to the Connection Managers and the errors will be cleared automatically.