October 19, 2009 at 2:27 pm
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
October 19, 2009 at 4:40 pm
Did you use an Expression to configure the ExcelFilePath property value of the Excel Connection Manager:
@[<variable name>]
October 19, 2009 at 8:01 pm
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!
October 19, 2009 at 8:16 pm
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?
October 19, 2009 at 8:38 pm
The path is long one with many spaces like t:\clients\projects\blah blah\report1
It is set to Excel 97-2003.
October 22, 2009 at 5:40 pm
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.
October 23, 2009 at 7:25 am
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