Today I encountered strange behavior when I was executing an SSIS package containing an SSIS Script task. When running the package in debug mode within Visual Studio 2015 the task executed as expected.
When I deployed the package to the SSIS catalog the package seems to run fine, the result is: succeeded. Unfortunately the C# code within the package didn't run. In the SSIS log I could see the package was executed and the package calls the script task. Unfortunately the code within the script task didn't run. The package even succeeded when I forced the script task to fail with the C# code:
Dts.TaskResult = (int)ScriptResults.Failure;
To resolve this issue I changed the project properties of the SSIS Project in Visual Studio to target the right version of SQL Server. Somehow it was set to SQL Server 2017 where it actually should be SQL Server 2016, in my case.
This will tell Visual Studio to build the project for the correct version of SQL Server including the correct libraries.
You can set the target deployment server by going to the properties of your SSIS project.
Go to "Configuration properties", select "General", change the "TargetServerVersion" to the version of your target SQL Server.
Additionally I needed to make sure I do a project deployment (deploy
all packages within the project) to the SSIS catalog instead of doing a
'single' package deployment. Unfortunately I can't clarify at the moment
why the script task won't run the code when I do a package deployment.
To do a project deployment: right click your SSIS project and click "deploy" .
Wrap-up: when your C# / VB code doesn't run when deployed to the SSIS
catalog and the package succeeds, you might want to take a look at your
TargetServerVersion and set it to the correct version. Additionally
make sure you do a project deployment instead of a package deployment.
please leave me a note if you know the reason why the code is executed
with project deployment and not with package deployment.