I have an SSIS package where I am reading Excel files and loading the data to SQL tables. Part of reading the Excel file is to check the value in a specific cell in each sheet and make sure that the value matches the sheet name. I have the following code in the script task (this is not all of the code, only the relevant part up to where the error happens):
public void Main()
{
//Declare and initialize variables
String FolderPath = Dts.Variables["User::FolderPath"].Value.ToString();
String FilePath = Dts.Variables["User::FolderPath"].Value.ToString() + Dts.Variables["User::strCurrentFileIn"].Value.ToString();
String TableName = Dts.Variables["User::TableName"].Value.ToString();
String SchemaName = Dts.Variables["User::SchemaName"].Value.ToString();
String CellToRead = Dts.Variables["User::CellToRead"].Value.ToString();
String CellName = "";
Int32 CellNumber;
var directory = new DirectoryInfo(FolderPath);
FileInfo files = new FileInfo(FilePath);
String FileLoaddate;
ConnectionManager cm;
SqlConnection myADONetConnection;
SqlCommand cmd;
cm = Dts.Connections["CP_RACO_ADO"];
myADONetConnection = (SqlConnection)cm.AcquireConnection(Dts.Transaction);
int intIndex = 1;
var alphanumericstring = new System.Text.RegularExpressions.Regex("(?<Alpha>[a-zA-Z]*)(?<Numeric>[0-9]*)");
var match = alphanumericstring.Match(CellToRead);
CellName = match.Groups["Alpha"].Value;
int.TryParse(match.Groups["Numeric"].Value, out CellNumber);
//Process current inbound file
FileLoaddate = DateTime.Now.ToString("MM/dd/yyyy hh:mm:ss");
//Create Excel Connection
string ConStr;
string LoadingFilename = files.Name;
string HDR;
HDR = "NO";
ConStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=\"" + FilePath + "\";Extended Properties=\"Excel 12.0;HDR=" + HDR + ";IMEX=1\"";
OleDbConnection cnn = new OleDbConnection(ConStr);
////Get Sheet Name
cnn.Open();
DataTable dtSheet = cnn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
string sheetname;
sheetname = "";
string ldsheetname = "";
//Get Row Count for each sheet
foreach (DataRow drSheet in dtSheet.Rows)
{
if (drSheet["TABLE_NAME"].ToString().Contains("$"))
{
sheetname = drSheet["TABLE_NAME"].ToString();
ldsheetname = sheetname.Replace("$", "");
ldsheetname = ldsheetname.Replace("'", "");
ldsheetname = ldsheetname.Replace(" ", "");
//Read the cell value from Excel sheet
OleDbCommand oCell = new OleDbCommand("select * from [" + sheetname + CellName + (CellNumber - 1).ToString() + ":" + CellToRead + "]", cnn);
OleDbDataAdapter daCell = new OleDbDataAdapter(oCell);
DataTable dtCell = new DataTable();
daCell.Fill(dtCell);
Everything works just fine, right up until the daCell.Fill(dtCell) statement. At that point, the script throws the following error:
System.Data.OleDb.OleDbException: The Microsoft Access database engine could not find the object ''BETTAREL, BRIAN J$'B8:B9'. Make sure the object exists and that you spell its name and the path name correctly.
Shortly after this section is the code that actually loads all of the Excel data into a table, and that part works. So why is it that loading an entire sheet at once works, but I can't read a single cell value? What is causing this error, and how do I fix this? I've tried adding an exclamation mark (like you'd find in Excel formulas), and that didn't work. I've tried removing the dollar sign, and that doesn't work. I've tried using a replace to get rid of the quotation marks, and that doesn't work.
Something is causing this, and no amount of googling or messing with the code seems to work. Anybody have any idea why this is happening, and/or what I need to do to get this to work?
