SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
parik sarkar
parik sarkar
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 21
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?
Stewart "Arturius" Campbell
Stewart "Arturius" Campbell
SSCrazy Eights
SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)

Group: General Forum Members
Points: 9039 Visits: 7283
A warning is SQL will not cause the SSIS package to fail.
I propose you create an output file for the step of the job running the SSIS package.
Once the step has run (and failed), check this output file. you may find the actual error further down the in the file.
it could be that you are using a value in e.g. a derived column, which could fail upon receiving a NULL value for validation et al

____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”
Suresh-209155
Suresh-209155
SSC Veteran
SSC Veteran (279 reputation)SSC Veteran (279 reputation)SSC Veteran (279 reputation)SSC Veteran (279 reputation)SSC Veteran (279 reputation)SSC Veteran (279 reputation)SSC Veteran (279 reputation)SSC Veteran (279 reputation)

Group: General Forum Members
Points: 279 Visits: 83
i have similar issue, a store procedure was called from ETL Packages.
when executed store procedure alone warning was shown on result window but executing from pacakges was failed due to this warning.
To resolve we have add Set ANSI_WARNINGS OFF
That is actually the default on SQL 2008 and 2012
.
Sioban Krzywicki
Sioban Krzywicki
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1869 Visits: 8090
Please see this MS reply. This is a known problem with 2008 & will not be fixed. This thread gives a workaround.
intermittent NULL failure

--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search