Geek Answers Handbook

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

SSIS does not work with an error message, as shown 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 Internal Client" Hresult: 0x00040EDA Description: "Warning: Null is excluded by aggregation or other SET operation."

In the study, we found that the Warning: Null value is eliminated by an aggregate or other SET operation. message Warning: Null value is eliminated by an aggregate or other SET operation. returned by a SQL query (2005), which runs the SSIS package as a source in the DFT to insert into the target 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 solved the problem by excluding the warning from the SQL server by changing the query:

FROM

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

For

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

However, we have few questions, as below, which we could not find an explanation and hoping to get an answer in this forum:

  • Why does a warning from SQL pop up before the SSIS package and causes the SSIS package to not work?

  • If we run the same package in all other dev and UAT environments with the same dataset, it works fine. We can see the warning shown in SQL Server Management Studio, however, SSIS will fail. However, the SSIS package does not work in our products. We do not understand the logic? Is there any warning threshold here?

+7
source share
2 answers

The SSIS package failed due to the following warning message is a known bug that many have reported on the Microsoft Connect website.

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

Link to the bug report on the Connect website.

Last updated by the Microsoft team on this 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 do not know the cause of the problem, and the Microsoft team also closed this error as unreproducible. One workaround listed on the Connect website is to add the following query before your query. Be sure to include a semicolon at the end.

 SET ANSI_WARNINGS OFF;

Learn more about ANSI_WARNINGS on MSDN.

+8
source

Same issue here, sql 2008r2. The particular data stream that this issue had was unstable for several months without any problems. I also could not find null data.

The checked database property "ANSI Warnings Enabled" is set to False. Added SET ANSI_WARNINGS OFF; to the beginning of the request, and it worked. So it looks like this error is ignoring the database setup.

0
source

More articles:


All Articles