Recently we came across an interesting issue. I guess this will not happen often, but I think it’s worth mentioning it as you can save a few hours or days of investigation of what’s happening and why.
The issue was the following:
- We used script task in our packages to perform some logic.
Everything worked fine even when we deployed package to SSIS server (project deployment mode).
- When package was performed it ended with success.
- But after a couple of runs we found out script component logic was not performed at all.
- We have simplified logic for C# code just to make some log message but no message could be found in SSIS Log.
We have spent a couple of hours of investigation but there was not any reason which would explain the behavior. Finally, we found a lead in some discussion which I cannot reference now as I’ve lost it. The hint was regarding a different version of SSMS used for deployment than VS for development. We have realized we work for corporate which has standardized build & deployment of SSIS. So, we tested this. When we deployed package directly from VS, script worked. When we deployed via standard process it didn’t.
The resolutions were simple. The whole company step by step migrated development and ran from 2014 to 2016 SQL server. (VS2013 to VS2015) but for build and deploy process they forgot to update VS 2013 to 2015, so our SQL 2016 (VS 2015) SSIS project was build in VS 2013 which without any warning somehow “cleared out” c# code due to a different version of .Net framework. Execution of the task then succeeded but did not perform any code. You have no issues with this setup until you use script task in your package.
Most subtle part of this story from my perspective – there is no single info about fail or error during the whole process. When you face such an issue please check if all components in your process have the right version which do not screw-up any part of your package.
Thank you for your article. It helped me a lot (sorry for my bad English). I have sql2016 and visual studio 2022, which generate DTSX for sql2022. After reading your article, I changed output to SQL2016 and script now are running.