Read Excel cell values with SSIS script task

Question

I am trying to read an Excel file via a SSIS ScriptTask to check for certain cell values in that worksheet.

In the code example you can see that the strSQL is set to "H4:H4" to only read one cell. This cell can only have a true or false value. Since I also need to check for a certain string value in B1 I wanted to extend this version.

  string filePath = "c:\\test\\testBoolean.XLSX";
  string tabName = "testSheet$";
  string strSQL = "Select * From [" + tabName + "H4:H4]";
  String strCn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source="
                  + filePath + ";Extended Properties=\"Excel 12.0;HDR=NO;IMEX=1\";";
  OleDbConnection cn = new OleDbConnection(strCn);
  int iCnt = 0;
  OleDbDataAdapter objAdapter = new OleDbDataAdapter(strSQL, cn);
  DataSet ds = new DataSet();
  objAdapter.Fill(ds, tabName);
  DataTable dt = ds.Tables[tabName];

  foreach (DataRow row in dt.Rows)
  {
      iCnt = iCnt + 1;
      // some processing....
  }

What I don't understand is why I get a boolean value with the above strSQL statement or with any statment containing the same row number like so:

string strSQL = "Select * From [" + tabName + "F4:H4]";

Debug-Output:

row.ItemArray[2]    false   object {bool}

But when I set a different range like this one:

string strSQL = "Select * From [" + tabName + "F1:H4]";

I loose the recognition of the bool value:

row.ItemArray[2]   "FALSE"  object {string}

I'd much rather like to use the bool value for other processing tasks.

How can I fix this in addition to also reading the B2 value?

Solution 1

Your connection string specified IMEX=1, which tells the driver to treat intermixed data types as text. (See the "Usage Considerations" section of the MSDN article Excel Connection Manager.)

Thus, when you specified a single row

string strSQL = "Select * From [" + tabName + "F4:H4]";

there was only one possible data type for the third column, and the driver was able to correctly infer it. However, when you specified multiple rows

string strSQL = "Select * From [" + tabName + "F1:H4]";

and any value in the range H1:H4 was not a bool, the driver translated all values in that column to strings.

Assuming that you do in fact have mixed data types in column H and only care about the values in two particular cells, the simplest solution is to query each cell individually. See Import a single Excel cell into SSIS for some ideas on how to do that.

Solution 2

I would clone most of the code to produce two separate SELECT statements to query the two different cells you are after with separate SQL statements.

Actually I would probably go further and shred the whole script into SSIS components e.g. Execute SQL Tasks or Data Flow Tasks.

Comments

[TonyC]

this actually makes sense :-) Thanks for the info - I overlooked the IMEX setting.