Send Excel SpreadSheet Filename to SSIS

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » Send Excel SpreadSheet Filename to SSIS

Send Excel SpreadSheet Filename to SSIS

Rate Topic

Display Mode

Topic Options

Author

Message

davidlcrooks

Posted Monday, October 19, 2009 2:27 PM

Grasshopper

Group: General Forum Members
Last Login: Tuesday, September 04, 2012 1:22 PM
Points: 12, Visits: 48

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

sabotta

Posted Monday, October 19, 2009 4:40 PM

SSC 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

davidlcrooks

Posted Monday, October 19, 2009 8:01 PM

Grasshopper

Group: General Forum Members
Last Login: Tuesday, September 04, 2012 1:22 PM
Points: 12, Visits: 48

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

sabotta

Posted Monday, October 19, 2009 8:16 PM

SSC 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

davidlcrooks

Posted Monday, October 19, 2009 8:38 PM

Grasshopper

Group: General Forum Members
Last Login: Tuesday, September 04, 2012 1:22 PM
Points: 12, Visits: 48

The path is long one with many spaces like t:\clients\projects\blah blah\report1

It is set to Excel 97-2003.

Post #805391

sabotta

Posted Thursday, October 22, 2009 5:40 PM

SSC 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

P Jones

Posted Friday, October 23, 2009 7:25 AM

Mr or Mrs. 500

Group: General Forum Members
Last Login: 2 days ago @ 7:35 AM
Points: 515, Visits: 1,016

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 »

Permissions