Hi

My company runs Web Services written in C# (running Windows 2012R2 / IIS 8.5) that use the SSIS API (via HTTPS) to execute packages on a remote 2012 SQL Server (running Windows 2012R2) and we are experiencing some performance issues when issuing a particular command via the API.

I am trying to understand exactly what happens when the Web Service issues the command to execute the package (asynchronously) as this our bottleneck - it can take up to 30 seconds to execute a package, yet the package may only actually take 5 seconds or less to complete.

In our C# code (listed below) we create the execution of the package with any parameters added and then perform the execute once we have the Execution Identifier. This is where the issue is; the call is made to run the package and the response takes around 30 seconds to complete but the packages finishes in a few seconds.

What I am trying to find out (as there is no documentation that I can find) is exactly what happens in SQL Server when this code is executed:

ExecutionOperation executionOperation = catalogSSISDB.Executions[executionIdentifier];

If I can understand what these API commands actually do, I may be able to troubleshoot the issue further but at the moment, we are scratching our heads.

Any help or guidance is greatly appreciated!

using System;
using System.Collections.Generic;
using System.Linq;
using System.Runtime.Serialization;
using System.ServiceModel;
using System.ServiceModel.Web;
using System.Text;
using Microsoft.SqlServer.Management.IntegrationServices;
using System.Data;
using System.Data.SqlClient;
using Microsoft.SqlServer.Management;
using System.Configuration;

// NOTE: You can use the "Rename" command on the "Refactor" menu to change the class name "Service" in code, svc and config file together.
public class Service : IService
{
    public void invokeSyncWebServiceName(input_WebServiceName input, out string correlationId, out ExecutionStateType executionState, out ResultStatusType resultStatus, out output_WebServiceName output)
    {
        output = new output_WebServiceName();
        correlationId = "-1";

        try
        {
            //Establish a connection to the SSIS server
            SqlConnection sqlSSISConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["SSISCatalogConnection"].ConnectionString);

            //Connect to the SSIS server
            IntegrationServices integrationServices = new IntegrationServices(sqlSSISConnection);
            Catalog catalogSSISDB = integrationServices.Catalogs["SSISDB"];

            //Get package from catalog
            PackageInfo myPackage = integrationServices.Catalogs["SSISDB"].Folders["FolderName"].Projects["ProjectName"].Packages["PackageName.dtsx"];

            //Set package parameters
            System.Collections.ObjectModel.Collection<PackageInfo.ExecutionValueParameterSet> executionValueParameterSet = new System.Collections.ObjectModel.Collection<PackageInfo.ExecutionValueParameterSet>();
            executionValueParameterSet.Add(new PackageInfo.ExecutionValueParameterSet { ParameterName = "Param1", ParameterValue = DateTime.Parse(input.Date1), ObjectType = 30 });
            executionValueParameterSet.Add(new PackageInfo.ExecutionValueParameterSet { ParameterName = "Param2", ParameterValue = input.Value1, ObjectType = 30 });
            executionValueParameterSet.Add(new PackageInfo.ExecutionValueParameterSet { ParameterName = "Param3", ParameterValue = input.Value2, ObjectType = 30 });

            //Get SSIS environment
            EnvironmentReference environmentReference = myPackage.Parent.References[ConfigurationManager.AppSettings["SSISEnvironment"], ConfigurationManager.AppSettings["SSISEnvironmentFolder"]];

            //Execute package
            long executionIdentifier = myPackage.Execute(false, environmentReference, executionValueParameterSet);

            //Set correlationID to SSIS executionID
            correlationId = executionIdentifier.ToString();

            //Execute package
            ExecutionOperation executionOperation = catalogSSISDB.Executions[executionIdentifier];

            //Refresh execution status
            executionOperation.Refresh();

            //Wait for the package to complete (if applicable)
            while (!executionOperation.Completed)
            {
                System.Threading.Thread.Sleep(5000);

                executionOperation.Refresh();
            }

            //Package completed
            executionState = ExecutionStateType.FINISHED;

            //Get completion status
            if (executionOperation.Status == Operation.ServerOperationStatus.Success)
            {
                resultStatus = ResultStatusType.OK;
            }
            else
            {
                resultStatus = ResultStatusType.ERROR;
            }

            //SSIS package execution results are persisted to a database
            //Connect to database to obtain package execution result
            SqlConnection sqlConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["PackageOutputConnection"].ConnectionString);
            sqlConnection.Open();
            string strSQLCommand = "SELECT package_output, result_status FROM package.execution_output WHERE execution_id = " + executionIdentifier.ToString();
            SqlCommand sqlCommand = new SqlCommand(strSQLCommand, sqlConnection);
            SqlDataReader sqlDataReader = sqlCommand.ExecuteReader();

            //If there's no row in the table for the execution the package completed successfully but do not return a result
            if (!sqlDataReader.HasRows)
            {
                output.Result = "";
            }
            else
            {
                //Get the result and set the web method response
                sqlDataReader.Read();
                output.Result = sqlDataReader.GetString(0);
                string strPackageResultStatus = sqlDataReader.GetString(1);
                if (strPackageResultStatus == "OK")
                {
                    resultStatus = ResultStatusType.OK;
                }
                else if (strPackageResultStatus == "WARNING")
                {
                    resultStatus = ResultStatusType.WARNING;
                }
                else
                {
                    resultStatus = ResultStatusType.ERROR;
                }
                sqlDataReader.Close();
                //Update the package.execution_output table to show the result has been returned
                string strSQLUpdate = "UPDATE package.execution_output SET output_delivered = 'Y' WHERE execution_id = " + executionIdentifier.ToString();
                SqlCommand sqlUpdateCommand = new SqlCommand(strSQLUpdate, sqlConnection);
                sqlUpdateCommand.ExecuteNonQuery();
            }

            sqlConnection.Close();
        }
        catch (Exception e)
        {
            output.Result = e.Message;
            resultStatus = ResultStatusType.ERROR;
            executionState = ExecutionStateType.FINISHED;
            DWF.Logging.ErrorLogging.LogError(e);
        }

        return;
    }
}

Hi Marc,

I am trying to involve someone more familiar with this topic for a further look at this issue. Sometime delay might be expected from the job transferring. Your patience is greatly appreciated.

Thank you for your understanding and support.

Regards,
Katherine Xiong

Hi Marc,

I think we need first to narrow down the issue:

1) The slowness happens due to the code? --> Print out the detail timestamp for each steps in your application.

2) The slowness happens due to SQL Server side -->Capture a SQL Server profiler trace on the SQL Server side to see the detail time of establish connection to SQL, get the package info from SSISDB, etc.,

3)The slowness happens due to executing package? --> Enable SSIS logging, please.

Please compare the timestamp of all three parts, and first narrow down where is culprit for the issue.

Regards,

Doris Ji