When executing the Open function. I received the following exception:
Microsoft Office Excel cannot access the file 'd:\testing\testing.xls'
There are several possible reasons:
• The file name or path does not exist
• The file is being used by another program.
• The workbook you are trying to save has the same name as a currently open workbook.
However, when I removed the directory information and just give it the file name: 'testing.xls' - I put testing.xls in to the same directory as the Windows Service- everything works fine, bizarre isn't it.
Research shows that automation is an issue with Windows Server 2008, specifically the 64-bit version. The 32bit version works, as well as windows 2003 32/64bit. I'm unsure at this point as to whether it affects only SERVICES or APPLICATION automation as well.
I dug in further and figured out the solution which is pretty crazy and actually motivated me to blog this for thousands of you who may run into this hair-pulling problem with SSIS and Excel.
For Windows 2008 Server x64: Create the following directory:
For Windows 2008 Server x86: Create the following directory:
Thats It!! Voila!! You are all set to go…..
Author : Debarchan(MSFT) SQL Developer Engineer, Microsoft
Reviewed by : Snehadeep(MSFT), SQL Developer Technical Lead , Microsoft