Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Send Excel SpreadSheet Filename to SSIS Expand / Collapse
Author
Message
Posted Monday, October 19, 2009 2:27 PM


Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, January 29, 2014 9:55 AM
Points: 12, Visits: 49
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
Post #805272
Posted Monday, October 19, 2009 4:40 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, January 31, 2013 1:50 PM
Points: 27, Visits: 123
Did you use an Expression to configure the ExcelFilePath property value of the Excel Connection Manager:

@[<variable name>]
Post #805323
Posted Monday, October 19, 2009 8:01 PM


Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, January 29, 2014 9:55 AM
Points: 12, Visits: 49
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!
Post #805377
Posted Monday, October 19, 2009 8:16 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, January 31, 2013 1:50 PM
Points: 27, Visits: 123
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?
Post #805384
Posted Monday, October 19, 2009 8:38 PM


Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, January 29, 2014 9:55 AM
Points: 12, Visits: 49
The path is long one with many spaces like t:\clients\projects\blah blah\report1

It is set to Excel 97-2003.


Post #805391
Posted Thursday, October 22, 2009 5:40 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, January 31, 2013 1:50 PM
Points: 27, Visits: 123
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.
Post #807519
Posted Friday, October 23, 2009 7:25 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, April 17, 2014 8:20 AM
Points: 547, Visits: 1,126
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.
Post #807865
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse