Why does a NULL aggregate warning in an SQL query causes the SSIS package to fail?

Question

An SSIS Package was failing with an error message as below:

Code: 0xC0202009 Source: DFT Populate ImageSummary OLE_SRC ProductImage [1] Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x00040EDA Description: "Warning: Null value is eliminated by an aggregate or other SET operation.".

On investigation, we have found that the message Warning: Null value is eliminated by an aggregate or other SET operation. is being returned by a SQL server (2005) query which the SSIS package runs as the source in DFT to insert into a destination table.

Select ProductID ,ImageTypeID ,DistinctImageCount ,ImageSize 
from 
    (select a.ProductID ,a.ImageTypeID ,a.DistinctImageCount ,a.ImageSize ,RANK() OVER (PARTITION BY a.ProductID, a.ImageTypeID ORDER BY a.ImageSize) As Ranker 
    from 
        (SELECT TOP 100 Percent spi.ProductID ,sit.ImageTypeID ,CAST(COUNT(DISTINCT spi2.ImageTypeID) as bit) DistinctImageCount ,CAST(spi2.Size as varchar(50)) as ImageSize 
        FROM Stage.ProductImage spi CROSS JOIN Reference.ImageType sit LEFT JOIN Stage.ProductImage spi2 ON spi.ProductID = spi2.ProductID AND sit.ImageTypeID = spi2.ImageTypeID 
        GROUP BY spi.ProductID, sit.ImageTypeID,spi2.Size 
        ORDER BY spi.ProductID, sit.ImageTypeID,spi2.Size
        )a
    )b 
where ranker = 1 
Order by ProductID,ImageTypeID

We have resolved the issue by eliminating the warning message from SQL server by modifying the query:

From

CAST(COUNT(DISTINCT spi2.ImageTypeID)as bit) DistinctImageCount

To

CAST(SUM(DISTINCT ISNULL(spi2.ImageTypeID,0)) as bit) DistinctImageCount.

However we have few questions as below which we couldn't find an explanation and hoping to get an answer on this forum:

Why does a warning from SQL bubbles up to the SSIS package and causes the SSIS package to fail?
If we run the same package in all other dev and UAT environments with the same data set, it works fine. We can see the warning showing up in the SQL Server Management Studio, however does not cause the SSIS to fail. However the SSIS package in our Production environment fails. We are failing to understand the logic? Is there any threshold of warnings?

For #2, that sounds suspiciously like a variance in patch levels between production and your dev & UAT. Can you confirm? — billinkc
Unfortunately they are all exactly the same! SQL Server 2005 SP4 CU3 has been applied across the board. — Parik Sarkar

Unknown Unknown · Accepted Answer · 2012-10-31T12:33:18

SSIS package failure due to the following warning message is a known bug that has been reported by many at Microsoft Connect website.

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

Link to the bug report on Connect website.

Microsoft team's last update on the issue:

This problem was resolved in SQL Server 2012 RTM (11.00.2100.60 or later).
There is no hotfix available for SQL Server 2005/2008/2008R2.

I don't know the cause of the issue and Microsoft team has also closed this bug as not reproducible. One of the workarounds listed on the Connect website is adding the following statement before your query. Please make sure to include the semicolon at the end.

SET ANSI_WARNINGS OFF;

Why does a NULL aggregate warning in an SQL query causes the SSIS package to fail?

From

To

2 Answers