SSIS Not Executing any Script Task

Question

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?

--UPDATE--

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!

Answer 1

I spend the day dealing with the same problem in vain, my temporal solution would be to run the SSIS package with a CMD command.

here is my example:

dtexec /ISSERVER "\"\SSISDB\project_____name\Main_Package.dtsx\"" /SERVER server___name /X86 /Par "\"$Project::var___name\"";"\"value___value\""

Answer 2

After several hours of researching I put some troubleshooting steps together so I wouldn't forget this event. I hope this helps someone else. I tried to include as much detail as possible for my reference, but not every single step is listed.

SSIS script task logging appears in the SSIS log table when running thru VS/SSDT but not when running package via SSMS or via SQL Server Agent

Follow the troubleshooting steps in the following order and test package execution in SSMS after each step. The majority of these steps require you be an administrator of both the SQL instance and the database server.

1. Verify you are executing the package as an account with local admin rights on the database server.

   • In most cases, the package will run as the SQL Agent service account unless the user executing the package is a local admin on the server. Note that redeploying at the project level may require Parameters or Connection Managers to be updated in SSMS following deployment if they were previously changed in SSMS.

2. Redeploy package at the project level instead of at the package level

   • In VS/SSDT, right click the project name and select Deploy
   • Follow your normal deployment steps

3. Verify the project is targeting the correct version of SQL Server

   • In VS/SSDT, right click the project name and select Properties
   • Expand Configuration Properties
   • Select General
   • Update the TargetServerVersion setting to the correct version of SQL Server
   • Redeploy package (step 1)

4. Verify the SSIS service running on the server is running as an account that has local admin rights

   • Open SQL Server Configuration Manager
   • Verify SQL Server Integration Services… is running as an account with local admin rights
     • To view local admins:
     • Go to Computer Management
     • System Tools > Local Users and Groups > Groups > Administrators

5. Verify SSIS Launch and Activation Permissions in DCOM Config

   • Open Component Services
   • Component Services > Computers > My Computer > DCOM Config
   • Select Microsoft SQL Server Integration Services…
   • Right click and choose properties
   • Select the Security Tab
   • In Launch and Activation Permissions select the Customize radio button then click Edit…
   • Select Administrators and verify all permissions are checked to Allow
   • Click OK

6. Check the Windows event log on the database server

   • Event Viewer > Windows Logs > System
   • Check if any errors exist for the DistributedCOM source
   • In my case it appears that the script is executing as the SYSTEM account instead of the SSIS service account. To resolve this you can restart SSIS from SQL Server Configuration Manager.