This one has me snake bit and I don't say that often. FTR, I'm running SQL Server 2014 and SQL Server Data Tools for Visual Studio 2013 aka SSDT.
I have a SSIS package that was deployed via Integration Services > MSDB. To be clear: I'm using Package Deployment Model (MSDB), not Project Deployment Model (SSISDB)(aka Integration Services Catalog). I have a SQL Job which calls this same package. I run the package from Data Tools (Debug > Start Debugging, or press F5). I run the SQL job. Same (proper) results. That's what we want.
Several weeks later, I was performing some code review and smoke testing the SQL Job as part of my process and the results wasn't quite what was spec'd out. There was extra left and right whitespace in some of the output data. I fired up Data Tools, executed the package with debugging and the output data was correct. The package and the job were producing different results. I checked the package flow and I can see the left and right whitespace was being trimmed out in the output data. I checked the package version. No changes. Nobody modified the package. Nobody modified the SQL Job, either. Something doesn't add up. I feel like there might be a bug here and I don't quite understand how it got introduced.
I fired up Integration Services and ran the package from MSDB and it produced the same incorrect results as executing the job. At least that part is consistent however incorrect. It's as if MSDB and, in turn, the SQL Job are running an older version of the package, despite the fact that nobody deployed an older version and nobody updated the package or the Job. To review: Data Tools good. MSDB and SQL Job bad.
To resolve (for now), I reapplied the same package via the Package Deployment Model to MSDB. I re-ran it from MSDB, after re-deploying, and the results were correct. I ran the job again and the results were correct.
But this has got me concerned because I don't know what caused it and I don't know when it will happen again. This job is critical, as its data is sent to an external SaaS provider and it's not that easy to clean up incorrect data when you don't have full control over the DB schema, unlike on-premise relational databases.
Has anyone else seen this kind of issue with SQL Server? What are some steps I can perform, besides contacting Microsoft, to troubleshoot and resolve this issue? Is this a bug with MSDB? Is it a bug with the package? Is this a known issue with the Package Deployment Model?
Thanks for your help in advance.