locked
Why does the Excel source think my query has parameters? RRS feed

  • Question

  • Part of my current project involves converting an Excel 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 thw 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.

    Tuesday, November 18, 2014 3:49 PM

Answers

  • 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.

    • Marked as answer by gorcq Wednesday, November 19, 2014 8:35 PM
    Wednesday, November 19, 2014 8:34 PM

All replies

  • I do not know why you do it this way, I suggest you simply extract the data to a staging table, transform (if needed) and then write from it to the TSV file.

    Why this error I guess it is perhaps of the $ sign


    Arthur

    MyBlog


    Twitter

    Tuesday, November 18, 2014 4:21 PM
  • Hi,

    For me Excel Data source is really tedious in SSIS.

    Whenever I work with Excel files specially when there is a case of multiple sheets I like to use .Net Provider for OleDb\Microsoft Jet... 

    Following URL explains how you can use it

    http://stackoverflow.com/questions/7411741/how-to-loop-through-excel-files-and-load-them-into-a-database-using-ssis-package

    Let me know how you get on....

    Prathy


    Prathy K

    Tuesday, November 18, 2014 4:31 PM
  • 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.

    • Marked as answer by gorcq Wednesday, November 19, 2014 8:35 PM
    Wednesday, November 19, 2014 8:34 PM