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

dcrooks gravatar image

41 2 2 2

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

1 answer: sort newest

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

Tom Staab gravatar image

Tom Staab
5.8k 6 8 10

Thanks! I got that. Now, I want to be able to pass the SSIS package a parameter which will be the name of the file that goes into the variable. Do I need to change the dtsConfig file with the new file name? TIA!
Oct 20, 2009 at 08:34 PM dcrooks
Assuming you are using dtexec to run the package, add this to the command line: /set \package.variables[myvariable].Value;myvalue
Oct 21, 2009 at 04:35 AM Tom Staab
I am now trying to call the package from C# and it is failing. I will try it from dtexec and see it that works. Thanks!
Oct 22, 2009 at 01:02 AM dcrooks
(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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

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: 3539 times

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