SSIS Error : oData VS_NEEDSNEWMETADATA Error When No Data Returned
I searched but didn't find anything specific to the issue I was having. My apologies if I overlooked it.
I have a scenario where I'm pulling data from an oData source and everything works fine. I have the oData source in a loop where I loop over each of our different companies and pull the data for that company. I'm doing this to reduce the volume of data that is being returned in each oData call.
But when the oData Service is called with parameters/filters that return no data I get the following and that is when the VS_NEEDSNEWMETADATA error is thrown. The image below shows what I get when no data is returned.
So the issue isn't that I have invalid metadata due to changes made to the service (adding/removing fields). It's that nothing is being returned so there is no Metadata. Now it's possible that this is an issue with the system that I'm pulling the oData from (SAP S4) and they way that system surfaces the oData call when there is no data??
Either way, trying to figure out a way to handle this within SSIS. I tried to set validate external metadata = false but the package still fails. I could also fix this by excluding those companies in the script but then once data does exists I'd have to remember to update the scripts and redeploy.
You've hit the nail on the head except your metadata is changing in the no data case because the shape is different - at least from the SSIS engine perspective.
You basically need to double invoke the OData source. First invocation will simply identify is data returned. If that evaluation is true, only then does the Data Flow start running.
How can you evaluate whether your OData will return data? You're likely looking at a Script Task and C#/VB.NET and hopefully the SAP data returns don't vary between this call and next call.
If that's the case, then you need to define an OnError event handler for the Data Flow, mark the System scoped variable Propagate to False and maybe even force a success status to be returned to get the loop to continue.