Monday, 10 August 2009

SQL Server Integration Services - How to check for the existence of an Excel Worksheet in SSIS

This code snippet shows you how to check for the existence of a worksheet in an Excel file. I had to do this because the data collection spreadsheet I was importing from would not add a $Warnings worksheet if there were no warnings for a particular set of data. The code below relies on the GetSchema() method of OLEDB connections to obtain schema information.


public class ScriptMain
{
public void Main()
{
string fileToTest;
string tableToTest;
string connectionString;
OleDbConnection excelConnection;
DataTable excelTables;
string currentTable;

fileToTest = Dts.Variables["ExcelFile"].Value.ToString();
tableToTest = Dts.Variables["ExcelTable"].Value.ToString();

Dts.Variables["ExcelTableExists"].Value = false;
if (File.Exists(fileToTest))
{
connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source=" + fileToTest + ";Extended Properties=Excel 8.0";
excelConnection = new OleDbConnection(connectionString);
excelConnection.Open();
excelTables = excelConnection.GetSchema("Tables");
foreach (DataRow excelTable in excelTables.Rows)
{
currentTable = excelTable["TABLE_NAME"].ToString();
if (currentTable == tableToTest)
{
Dts.Variables["ExcelTableExists"].Value = true;
}
}
}

Dts.TaskResult = (int)ScriptResults.Success;

}
}


For more information, see http://technet.microsoft.com/en-us/library/ms403358.aspx#example1

No comments: