July 23, 2012 at 2:48 pm
Hello All,
My SSIS package is failing with "Warning: Null value is eliminated by an aggregate or other SET operation.".
When I search it in google, they are suggesting to add SET ANSI_WARNINGS OFF but if the query has a parameter,it gives syntax error.
Can any one faced the same kind of issue and find any solution,please help me.
Thanks,
July 24, 2012 at 1:44 am
What exactly are you doing in the package?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
July 24, 2012 at 7:18 am
In my package, I am collecting the values from a table into a variable using execute sql task and then passing those values to for each loop.
It will load the data from sql database to excel with that parameter value like select * from table where column1=?.
When I execute that query in management studio ,its working fine but the package is failing with that error.
Here one more problem is the data is loading for a few of the parameter values and then it failed saying that error.
July 25, 2012 at 9:33 am
I got this error just yesterday.
For me, it occured when I used a statement like the following:
SET @value = (SELECT somevalue FROM sometable WHERE somethingistrue) --hopefully just pulls a single value
That got the error because it was returning NULL and attempting to set @value = NULL. Didn't like that in a SET situation, hence the error. It wasn't a hard bomb so it kept going. The settings are such that NULL concatination with other value yields NULL, so I got blank lines for a print statement instead of a line of values.
I got around it by doing the following:
SET @value = ISNULL((SELECT somevalue FROM sometable WHERE somethingistrue),'')
This replaced a returned value of null with a blank. This made the line print, and the error went away, since it was able to set @value to a blank, instead of a null value.
Hope it helps.
July 25, 2012 at 9:35 am
I would bet that if you are doing the aggregate part, it is essentially the same. Change the SUM(something) or whatever it is to ISNULL(SUM(something),0) or something similar and see if it goes away.
July 25, 2012 at 9:39 am
vikingDBA (7/25/2012)
I would bet that if you are doing the aggregate part, it is essentially the same. Change the SUM(something) or whatever it is to ISNULL(SUM(something),0) or something similar and see if it goes away.
You'll still get the warning about null values being ignorded with this code. You would need to do this:
SUM(ISNULL(something,0))
July 25, 2012 at 9:40 am
Thank you all for your reply.
My problem was solved.
My mistake is I am using SET ANSI_WARNINGS OFF, I am missing a colon at the end.
When I put a colon, SET ANSI_WARNINGS OFF; its working fine.
I am not getting the warning again and my package is working fine now.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply