How to display the execution/process of the SSIS in ASP.NET

Is it possible to display the process of execution of SSIS (run in SQL Server) in ASP.NET? For Example, The SSIS Job is for the extraction of files once the user clicked the button it triggered the stored procedure to run a SSIS Job(this part is okay already executed successfully), The next problem is I have no idea how to process of execution of SSIS in the screen of the user. So that, while extracting the user see the every successful task of the requested extract files.

Any ideas/reference of how to execute this.

This is the successful Job

This is the process of the SSIS

This is the user screen

This is the sample successful SSIS

To triggered the stored procedure in SQL

public static void ExecuteStoredProcedureINVM(string sSQLCon, string name)
    {
        SqlConnection sqlCon = null;
        SqlDataReader rdr = null;
        sqlCon = new SqlConnection(sSQLCon);
        SqlCommand cmd = new SqlCommand("dbo.execute_ssis_INVM_Export", sqlCon);
        cmd.CommandType = CommandType.StoredProcedure;
        sqlCon.Open();
        rdr = cmd.ExecuteReader();
         // This part I want to display the successful task on the executing package is it possible?
        Globals.sSSIS_TDMAT_STATUS = "Executing Package . . . wait several Minutes.";
    }

this is the stored procedure

Solution 1:

Assuming you're using the SSIS catalog to run the job, you just need to know the execution_id, and then you can query from the database like:

select top 1000
   e.execution_id
  ,e.start_time execution_start_time
  ,e.package_name
  ,m.message_type
  ,m.message
  ,case when message_type = 140 then cast(m.message as xml) else null end mesage_xml
from [catalog].[event_messages] m
join [catalog].executions e
  on m.operation_id = e.execution_id
where e.execution_id = @execution_id
ORDER BY m.message_time desc

A good way to get the execution_id is to skip the Agent job and kick off the execution directly, eg

Declare @execution_id bigint

declare @folder_name nvarchar(200) =N'MyFolder';
declare @project_name nvarchar(200) =N'MyProject';
declare @package_name nvarchar(200) = N'Package.dtsx';
DECLARE @logging_level smallint = 3 --3 is verbose

EXEC [SSISDB].[catalog].[create_execution] @package_name=@package_name, 
                                           @execution_id=@execution_id OUTPUT, 
                                           @folder_name=@folder_name, 
                                           @project_name=@project_name, 
                                           @use32bitruntime=False, 
                                           @reference_id=1, 
                                           @useanyworker=True, 
                                           @runinscaleout=True
Select @execution_id execution_id

EXEC [SSISDB].[catalog].[set_execution_parameter_value] @execution_id,  
                                                        @object_type=50, 
                                                        @parameter_name=N'LOGGING_LEVEL', 
                                                        @parameter_value=@logging_level

EXEC [SSISDB].[catalog].[start_execution] @execution_id,  @retry_count=0

But if you know there's only one instance of the package running, you can always just filter for that.