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.”.

在调查中,我们发现聚合或其他SET操作消除了警告：空值的消息.由sql Server(2005)查询返回,SSIS包作为DFT中的源运行以插入到目标表中.

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,spi2.Size 
        ORDER BY spi.ProductID,spi2.Size
        )a
    )b 
where ranker = 1 
Order by ProductID,ImageTypeID

我们通过修改查询来消除sql Server的警告消息,从而解决了这个问题：

从

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

至

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

但是我们几乎没有问题,我们无法找到解释并希望在此论坛上得到答案：

>为什么来自sql的警告会冒泡到SSIS包并导致
SSIS包失败了吗？
>如果我们在所有其他开发和UAT环境中运行相同的包
使用相同的数据集,它工作正常.我们可以看到警告
然而,在sql Server Management Studio中显示却没有
导致SSIS失败.但是我们生产中的SSIS包
环境失败.我们没有理解逻辑？在那儿
任何警告门槛？