ok, any plans on getting a new job, what in the world?? Please make us 5 course meal with wooden utensils and you cant use any fire. When you open your SSIS package in visual studio then go to
Project --> YourProject Properties and then under COnfiguration Properties --> Debugging
What do you see for Run64BitRunTime value?
This is a problem with Excel and SSIS...and it isn't easy to resolve in all cases. In order for you to be able to do anything with Excel - SSIS and the Excel driver on the system where it is being executed must be the same architecture.
So...if you have the 32-bit ACE OLEDB driver installed on the server then your package must be run from the 32-bit subsystem. If you have the 64-bit ACE OLEDB driver installed on the server then it must be run from the x64 subsystem.
In your case - it appears that the driver is not installed for either architecture. You need to work with the server team and have them install the 64-bit version so you can execute your packages on the server.
Yosiasz - I set that to 32-bi thinking it would solve the problem...oh and I am working on the new job.
Jeff - That being the case, any idea why I can create another package that also uses an Excel file as the source without any issues? The are both deployed to the same catalog.
You have another package (in the same project?) - deployed to the same server and that package is working with an Excel file as the source? If so then the problem isn't with the environment - it must be the package. Something in that package is causing issues - could the package have been designed to work with Excel 2003 files and you are trying to read an Excel 2010 (or higher) version?
Sorry - I have a separate solution/project that I created when I was getting the conditional split error. I wanted to test just a simple data flow task. Once it worked I added the conditional split and it failed. I then used that solution/project until I was able to get passed the other errors I was having.
Interesting - I just checked the project that works. The Excel connection in that package is pointing to a .xlsx file but I mistakenly selected 97-2003 version....but it works!
I will play around with the file extensions and the connection manager version tomorrow.
Thanks again for the help.
Scott
So someone on our server team installed the Access runtime but hasn't told me which version yet. I don't get the same error any longer but I do get a permissions error saying:
the Microsoft Access database engine cannot open or write to the file "{file path}". It is already open exclusively by another user, or you need permission to view and write its data.
The file is not open by anyone and I have permission to the folder it is located in. If I am not mistaken the package is executed using the account that is logged into SSMS, correct?
So it works if I move the Excel file to a local share, but not a remote one.
local share on the server? what user is this integration service running as? is it a domain user?
No...the package will be run in the context of the account running SQL Server unless you define a proxy acount.
Yes - that is what I ended up doing. This is finally working as expected. Thank you both for sticking with me and helping. This has been a good learning experience.