aharuray (8/19/2008)
My dtexec gives error as it says WEBSERVICE cannot run with this edition. But I have requested access to this folder where the excel files exist. May be it is a permission issue, thats why it works from one folder (for flat file) but not from another (excel file). I will update you as soon as I am able to test this.Thanks for the help on this.
Newbee - You can find options if you select Properties of Package (for 64 bit) or for Excel (for JET engine option).
Hmmm...some suggestions:
1. Definitely confirm that the SQL Agent that will be executing the package has permission to read the Excel file.
2. Confirm that the output of the Webservice is really a good Excel file. This might be a crazy notion, but I've seen some cases where a service (not necessarily a webservice) creates an XLS file from a database, but the exported file is really some old Excel format (like 2.0 or something) such that the Jet provider was of no use. We had to change our export service to save as either CSV or TXT or some old DBF because XLS was just not right. Not sure how you'd check this...maybe open the export in Excel, save/overwrite the file making sure the XLS file-type is something newer and try your SSIS package again? Or maybe try linking to the Excel file from Access using the Jet provider? Or create a UDL on your desktop and see if you can connect to the Excel file? Just some thoughts. The point is to confirm your Excel file is good.
3. See if the JET driver is even installed on the server by creating a DSN or something to the Excel file (on th server). If it works, examine the connection string of the DSN and see how it compares to the one being created in your SSIS package.
I'm really just grabbing at straws here...hope it helps.