I've got a data flow task where the source is a hard-coded parameterized query, i.e.
SELECT ColA, ColB, ColC
WHERE LastModifiedNumericDate > ?
(All of the dates are stored as integers, i.e. 20140412.)
The value for the parameter is 0 (zero) and should return every row but it isn't returning anything although it is "successful". If I cut and paste the query into a query window and substitute 0 for the ? it returns every row.
When I run the package through the debugger and break at that data flow task, the local variable holding the parameter value is set to 0 and is properly mapped. I did the exact same thing for TABLE_HDR earlier in the same package that ran successfully and returned all of the rows. It's a pretty basic "give-me-everything-since-the-last-time-I-asked" technique that I've used throughout the ETL and it works everywhere except for this one instance.
When I run the exact same package in a parallel test environment it works fine. The only difference between the two environments is that the source that isn't working has a few thousand more rows.
I'm stumped. I don't know what else to look for. If I had to guess, I'd say that somehow the parameter value has been reset to NULL but I don't really understand how that could happen.
Creating a stored procedure isn't an option because I can't write to the source system and the network guys won't let me create a linked server.
"Beliefs" get in the way of learning.