Folks, yet another stumble with SSIS and Excel. This time I am using Microsoft Office 11.0 Object Library. The code runs fine on Windows Server 2003.

Below is the code sample.

=======================================================

using Microsoft.Office.Interop.Excel;

using Excel = Microsoft.Office.Interop.Excel;

using System.Runtime.InteropServices;

Excel.Application m_ExcelApplication = new Excel.Application();

m_ExcelApplication.Visible = false;

m_ExcelApplication.UserControl = false;

m_ExcelApplication.Application.ScreenUpdating = false;

m_ExcelApplication.DisplayAlerts = false;

string strFileName = @"d:\testing\testing.xls";

Excel.Workbook m_ExcelWorkBook = m_ExcelApplication.Workbooks.Open(strFileName, //FileName

2, //UpdateLinks

bReadOnly, //ReadOnly

Type.Missing, //Format

Type.Missing, //Password

Type.Missing, //WriteResPassword

true, //IgnoreReadOnlyRecommended

Type.Missing, //Origin

Type.Missing, //Delimiter

false, //Editable

Type.Missing, //Notify

Type.Missing, //Converter

Type.Missing, //AddToMru

Type.Missing, //Local

Type.Missing //CurruptLoad

);

=======================================================

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.

SOLUTION:

For Windows 2008 Server x64: Create the following directory:

C:\Windows\SysWOW64\config\systemprofile\Desktop

For Windows 2008 Server x86: Create the following directory:

C:\Windows\System32\config\systemprofile\Desktop

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