Edit Question - KnowledgeHub

Title Body

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:

Tags (comma-separated) Cancel