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
Answer by Tom Staab ·
You need to create an Excel connection manager. Temporarily hard-code the path to any workbook. Then bring up the properties for the new connection. Select expressions, and add an expression for ExcelFilePath. Set the expression equal to your variable. Then, when you create your data flow source using that connection, the table or view name will be a worksheet name.