Category Archives: Integration services 2005/2008

MS, seriously!!! SSIS and System.Runtime.InteropServices.COMException (0x800A03EC)

Let me describe my problem. Maybe you have the same, if you have googled for the error code, and found this page.

So, I’m downloading an old Excel file from a German webpage. The Excel file is in a spreadsheetML format, which is impossible to read from SSIS.

No worries, I can easily with a script task, convert the file to CSV, using C# and the Excel API. Like so

….
using MS.Internal.Xml;
using Microsoft.Office.Interop.Excel;
…….

Microsoft.Office.Interop.Excel.Workbook wb1 ;
Microsoft.Office.Interop.Excel.Application wb2 =
new Microsoft.Office.Interop.Excel.Application();
wb2.DisplayAlerts = false;
wb1 = (Microsoft.Office.Interop.Excel.Workbook)wb2.Workbooks._Open(filename, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);

if (wb1.FileFormat == Microsoft.Office.Interop.Excel.XlFileFormat.xlXMLSpreadsheet)
{
wb1.SaveAs(filename.Replace(“.xls”,”.csv”) , Microsoft.Office.Interop.Excel.XlFileFormat.xlCSVMSDOS , Type.Missing, Type.Missing, false, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange,false,false,false,false,false);
}
else
{
wb2.Workbooks.Close();
}
wb2.Workbooks.Close();

Now I can read the data with a SSIS CSV data source. This Works fine, when I run my SSIS package from BIDS. When I put it in to production, and run the package with the SQL server agent, I get the following error:

Service cannot be started. System.Runtime.InteropServices.COMException (0x800A03EC): Microsoft Office Excel cannot access the file ‘c:\XXXX’. 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.

None of the above is my problem. I think I tried everything to find a solution to the problem. It turns out, that it is a bug in windows server 2008. There are some different solutions on the web, which include changing the registry values on the server.

Since I’m a consultant, and hence an expensive resource, I would like not to fuck anything up, playing around with registry values.

I found an easier fix however. Apparently, the folder C:\Windows\SysWOW64\config\systemprofile\Desktop has been removed from Windows server 2008. It was present in windows server 2003.

When I created the folder, everything worked fine. Apparently the desktop folder is necessary, if you want to manipulate Excel files with code from the SQL server agent. Scary.

So, the answer: if you have problems manipulating office files from code executed from the SQL server agent on windows server 2008 create the folder C:\Windows\SysWOW64\config\systemprofile\Desktop (C:\Windows\System32\config\systemprofile\Desktop if it is 32bit)

That was it for now. Bye bye:-)

Warning: Null value is eliminated by an aggregate or other SET operation

I just had a SSIS package that had been running every day for the last couple of years without any problems, which failed on me with the following error message:

Warning: Null value is eliminated by an aggregate or other SET operation

The SQL source had a group by, where I was taking a minimum on some datetime fields. I needed it to return NULL values because I had some additional logic that took these NULL values into account.

What was even stranger was, that when I ran the package by double clicking it on the server, it completed with success.

So apparently a warning from the SQL server causes an error in SSIS, but only sometimes….. Nice….

The solution is to either change your SQL, so you don’t get NULL values, by for instance use COALESCE or ISNULL, or if you can’t do that, put this statement in front of your SQL : SET ANSI_WARNINGS OFF

Read more about the problem/bug here:
https://connect.microsoft.com/SQLServer/feedback/details/483175/failure-because-of-warning-about-null-aggregation

Error: Could not get a list of SSIS packages from the project.

When I was working with a SSIS project at a customer, I got the following error.

Error: Could not get a list of SSIS packages from the project.

I had CreateDeploymentUtility=true.

That error description apparently indicate, that there is an error in the dtproj file.

The solution: Set CreateDeploymentUtility=false, and then build the project again. The you will get a specific error telling you what is wrong in the  dtproj file. Correct the error, and enable CreateDeploymentUtility again.