Excel SpreadSheet variable Filename to SSIS

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.

http://www.mssqltips.com/sqlservertip/1930/use-ssis-to-import-one-cell-of-an-excel-file-into-sql-server/