Why does the Excel source think my query has parameters?

Question

Part of my current project involves converting an Excel 2007 spreadsheet having a number of tabs into an equal number of pipe-delimited flat files using an SSIS package. I've got one data flow task that opens up the excel file and selects the names of all the tabs into and object variable. A second data flow task inside a for each loop attempts to read the first 32 columns from each tab. The excel source in the second data flow task is set up to use a sql command from a variable, and the variable is an expression that substitutes the tab name into an otherwise-constant sql statement like this:

SELECT F1, F2, F3, F4, F5, F6, F7, F8, F9, F10, F11, F12, F13, F14, F15, F16, F17,
    F18, F19, F20, F21, F22, F23, F24, F25, F26, F27, F28, F29, F30, F31, F32
FROM [Securities Portfolio$]

The "First row has column names" box in the excel connection manager is not marked.

Here's the thing: this works for most of the tabs. The one above works. However, three of them are causing the following error:

The SQL command requires 2 parameters, but the parameter mapping only has 0 parameters.

I've modified the package to print all of the SQL statements used in the log file. The only difference is in the tab name. None have any question marks, which is what I thought SSIS used to mark parameters. I can't figure out what causes this, or even how the failing SQL queries are different than the ones that work:

SELECT F1, F2, F3, F4, F5, F6, F7, F8, F9, F10, F11, F12, F13, F14, F15, F16, F17,
    F18, F19, F20, F21, F22, F23, F24, F25, F26, F27, F28, F29, F30, F31, F32
FROM [Capital - Part1$]
SELECT F1, F2, F3, F4, F5, F6, F7, F8, F9, F10, F11, F12, F13, F14, F15, F16, F17,
    F18, F19, F20, F21, F22, F23, F24, F25, F26, F27, F28, F29, F30, F31, F32
FROM [Capital - Part2$]
SELECT F1, F2, F3, F4, F5, F6, F7, F8, F9, F10, F11, F12, F13, F14, F15, F16, F17,
    F18, F19, F20, F21, F22, F23, F24, F25, F26, F27, F28, F29, F30, F31, F32
FROM [Securities Sources$]

Any help would be appreciated.

Answer 1

OK, I figured it out, and will write it here in case someone else has a similar issue, because I spent several hours on Google trying to figure out what was going on.

It turns out that not all of the worksheets actually HAVE 32 columns. It did not occur to me at first that this could be an issue, because when you open an Excel file in Excel, there are always more columns if you just scroll over more. However, the way Microsoft Jet reads Excel files, each worksheet has a fixed number of columns.

The worksheets that were causing the issue were the ones that have fewer than 32 columns. If there are only 30 columns and I try to select F32, Microsoft Jet assumes that F32 is supposed to be the name of a parameter. As far as I can tell, SSIS 2008 does not allow you to set up parameter values for use with Jet, but Jet will nevertheless parse your query and determine that there are parameters needed if it can't find some of the column names.

To get around this, I changed the step that gets the worksheet names to also return the number of columns in each worksheet, and then changed the query to return NULL for any columns that don't exist. So, for a worksheet having only 30 columns, I now run this:

SELECT F1, F2, F3, F4, F5, F6, F7, F8, F9, F10, F11, F12, F13, F14, F15, F16, F17, F18,
F19, F20, F21, F22, F23, F24, F25, F26, F27, F28, F29, F30, NULL AS F31, NULL AS F32
FROM [Capital - Part1$]

And that fixes it.