SSIS Script Task not Executing

Question

I have an SSIS script task package executing using SQL Server Agent Job.Initially I got the following error.

The application-specific permission settings do not grant Local Activation permission for the   COM Server application with CLSID 
{E0628A3F-D40D-4C78-87B9-98733CDF11F2}
and APPID 
{347F1A3E-7950-45F6-8681-43C056BDE1BD}
to the user NT SERVICE\SQLSERVERAGENT SID (S-1-5-80-344959196-2060754871-2302487193-2804545603-1466107430) from address LocalHost (Using LRPC) running in the application container Unavailable SID (Unavailable). This security permission can be modified using the Component Services administrative tool.

Then I set the Admin privileges to NT SERVICE\SQLSERVERAGENT. Now there is no error message in the event log. However, the script task is not executing as expected.

Answer 1

1: try to run the job with Admin.

2: put some auditing in your own package as at the beginning and at the end so you can catch the message. This is usually done as good practice. You can use for example execute sql task that writes message in a table : one for success and one for failure. You have on failure event (event handlers) where you can create task.You can put one task in the beginning to send a message if it starts. and a question: what is going on if you run it manually? is it only with a job that you have an issue?

3.A workaround (if the issue is in the job) will be to create a procedure that runs it so the job starts the stored procedure.

Here is a link to ms manual how to https://learn.microsoft.com/en-us/sql/integration-services/ssis-quickstart-run-tsql-ssms?view=sql-server-ver16

and here is the script:

Declare @execution_id bigint
EXEC [SSISDB].[catalog].[create_execution] @package_name=N'Package.dtsx',
    @execution_id=@execution_id OUTPUT,
    @folder_name=N'Deployed Projects',
      @project_name=N'Integration Services Project1',
    @use32bitruntime=False,
      @reference_id=Null
Select @execution_id
DECLARE @var0 smallint = 1
EXEC [SSISDB].[catalog].[set_execution_parameter_value] @execution_id,
    @object_type=50,
      @parameter_name=N'LOGGING_LEVEL',
      @parameter_value=@var0
EXEC [SSISDB].[catalog].[start_execution] @execution_id
GO

4.You can go to your package, right click, reports -> all executions and you can check if it actually runs.