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

more ▼

asked Oct 20, 2009 at 11:47 AM in Default

avatar image

41 2 2 4

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

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.

more ▼

answered Oct 20, 2009 at 01:46 PM

avatar image

Tom Staab ♦
14.5k 7 14 18

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Oct 20, 2009 at 11:47 AM

Seen: 3861 times

Last Updated: Oct 20, 2009 at 11:50 AM

Copyright 2016 Redgate Software. Privacy Policy