Send Excel SpreadSheet Filename to SSIS

  • I guess this is the right place for this question?

    I am trying to pass a variable (filename with path) to a package to send that variable to SSIS as an Excel Source. I have a variable defined as a string and a default value where an Excel file is located (verified). When I change the Data Access Mode to table name or variable and then select the variable it throws the error that opening the rowset failed. Any ideas? TIA!

    David L. Crooks

  • Did you use an Expression to configure the ExcelFilePath property value of the Excel Connection Manager:

    @[<variable name>]

  • No, I had thought about something like that after I posted the question.

    Now, I get an error to Please provide a file extension in the Excel file path... I did try to brute force it but then it said it was an invalid file name.

    Thanks for the reply!

  • What is the file path (full path) that you're storing in the variable? And, is this an Excel '97 file or a later version?

  • The path is long one with many spaces like t:\clients\projects\blah blah\report1

    It is set to Excel 97-2003.

  • Without being able to see the package you've created, I can't be certain what the problem may be. Have you confirmed that the Excel file you're using is not corrupt (trying opening it in Excel)?

    I created a simple package (using BIDs on a 32-bit machine) that contains an Excel Source & OLE DB Destination, Excel Connection Manager, and an SSIS variable that stores the full path to the Excel file. The package runs successfully. Here are the steps I followed. I hope this is helpful in resolving the error you're getting:

    1. Added Excel Source.

    2. Added Excel Connection Manager, clicked Edit, and entered the full file path in the Excel file path box (C:\Work\Excel Folder\TestBook97.xls). Saved changes.

    3. Configured the Excel Source to point to the connection manager.

    4. Added a string variable to the package, confirmed that the variable scope was Package, and filled in the variable value (C:\Work\Excel Folder\TestBook97.xls).

    5. Added an expression to the Excel Connection Manager, to set value of the ExcelFilePath property using the string variable.

    6. Connected the Source to the Destination.

  • Set the delay validation property to true to stop it checking the connection file before the variable is properly loaded. I can't remember if it's on the connection manager or the actual component that needs it.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply