I have a simple SSIS package written in Visual Studio 2015 in a Project Deployment model solution. The package includes a Script Task with code written in C# and performs a very simple task. This script targets .NET Framework version 4.5.2. I verified this version of .NET is installed on the SQL Server machine.
When I run the package in my local development environment it runs fine and does exactly what I need it to do.
I deployed it to the Integration Services Catalogs in the DEV SQL Instance and setup a SQL Server Agent Job to execute the package. I executed the job but it failed in a step past the script task. The failure was due to the Script Task not doing it's job. A check of the SSIS Package execution messages showed the Script Task step completed with no errors. I checked the event logs and found no errors there. I double checked the C# code. I modified the script task code to also generate an Information Message (Dts.Events.FireInformation) just to make sure the script task was actually firing. I changed the SSIS logging in the job to Verbose as well. I tried several other methods to validate the C# code was being called and executed. I turned antivirus off and re-ran with no change.
I’m convinced that the Script Task is just not executing the C# code in the Script Task. I took the same basic c# script code and created a console app and successfully ran it on the server so I know the .NET code works on the server. I looked at the access permissions in Component Services and the permissions seem fine. No errors are being thrown in SSIS or the event logs so it's been difficult to figure out what the problem is.
I’m not sure what else to check. Does anyone know if what else I can check that might be preventing Script Tasks from being executed by SSIS or the SQL Agent?
So after spending over 3 days trying to figure this out I decided to approach this differently.
On a hunch I decided to install VS 2017 and SSDT 2017 to see if the upgraded development environment would make any difference. It works perfectly on the server after upgrading the project to 2017 and rebuilding the script task.
So apparently there was some sort of issue with my development environment or the versioning of the compiled code.
Live and learn I guess!