SSIS lost stored procedure metadata

We have an SSIS pkg that runs a stored procedure in an OLEDB source and writes the output to a file. The stored procedure has a condition in that it won't do anything if it's a certain day of the week. When it does produce data, it steps through several temp tables to generate the final data (which comes entirely from temp tables); the stored proc has SET FMTONLY OFF enabled at the start so it can send metadata to SSIS. (The two servers involved are SQL Server 2008 on Windows 2008 Server boxes.)

Last night (the days it should not generate data are Sunday/Monday, and this was Sunday ni开发者_如何学Pythonght when it was scheduled to run (it's part of a larger job, so it'll run any time that parent runs)), the pkg started complaining that it cannot find the metadata for the stored proc. When you run the proc directly on the server in SQL Mgmt Studio, it returns no rows but also no column headers. However, the exact same code and data in our development environment doesn't do this; it returns no data (correct) but it does show column headers (also correct) so the dev version of the pkg doesn't complain about metadata.

I also tried the trick of adding in 'dummy' metadata at the top of the proc inside a never-executed piece of code (if 1 = 0 begin select cast(null as varchar(10)) as column1 end), but that didn't do diddly.

I'm left to thinking there has to be some sort of setting difference in production versus development that would not keep metadata cached in production but will keep it in development. Anyone have any idea where to start looking?

I don't think this is a SSIS problem, nor a metadata problem. You said that when run in Management Studio, the proc returns an empty data set in development (column headers only) and nothing at all in production. This is your issue, the fact that it's possible for the proc to return nothing rather than an empty data set, and this is why SSIS is complaining about the metadata.

Double check that your proc and data are exactly the same in production and development. Does every code path return something (i.e. could something be causing production to hit a code path which returns nothing at all)?