question

dcrooks avatar image
dcrooks asked

SQL Server 2008: SSIS pass variable fails for Excel filename

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

sql-server-2008ssisexcel
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Tom Staab avatar image
Tom Staab answered

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.

10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.