Tuesday, 17 September 2013

Specifying the timeout for SSIS 2012 package execution

This is a follow up to my post from last year about executing packages in the SSIS catalog programmatically.  The main problem that makes it unworkable for production scenarios is that packages cannot be run synchronously if they take longer than 30 seconds.  The reason for this is probably because the Microsoft.SqlServer.Management.IntegrationServices.dll assembly is simply using a SqlCommand object, which has a default timeout of 30 seconds, to call the SSIS catalog stored procedures.  Unfortunately the assembly doesn’t provide any way to get at the timeout property.

Unlike the assembly, the underlying stored procedures are fairly well documented.  So it’s not a huge leap to create your own wrapper to call the execute stored procedure, but with a way of changing the timeout value.  The least intrusive way to do this is to use extension methods to provide alternative versions of the Execute methods that allow synchronous execution:

public static class Extensions
{
    #region Private constants

    private const string CREATE_EXECUTION = @"[catalog].[create_execution]";
    private const string SET_EXECUTION_PARAMETER_VALUE = @"[catalog].[set_execution_parameter_value]";
    private const string SET_EXECUTION_PROPERTY_OVERRIDE_VALUE = @"[catalog].[set_execution_property_override_value]";
    private const string START_EXECUTION = @"[catalog].[start_execution]";

    private const string EXECUTION_ID = @"@execution_id";
    private const string OBJECT_TYPE = @"@object_type";
    private const string PARAMETER_NAME = @"@parameter_name";
    private const string PARAMETER_VALUE = @"@parameter_value";
    private const string PACKAGE_NAME = @"@package_name";
    private const string FOLDER_NAME = @"@folder_name";
    private const string PROJECT_NAME = @"@project_name";
    private const string USE32BITRUNTIME = @"@use32bitruntime";
    private const string REFERENCE_ID = @"@reference_id";
    private const string PROPERTY_PATH = @"@property_path";
    private const string PROPERTY_VALUE = @"@property_value";
    private const string SENSITIVE = @"@sensitive";

    #endregion

    #region Public extension methods

    public static long Execute(this PackageInfo packageInfo,
        bool use32RuntimeOn64, EnvironmentReference reference,
        Collection<PackageInfo.ExecutionValueParameterSet> setValueParameters,
        int commandTimeout)
    {
        return packageInfo.Execute(use32RuntimeOn64, reference, setValueParameters, null, commandTimeout);
    }

    public static long Execute(this PackageInfo packageInfo,
        bool use32RuntimeOn64, EnvironmentReference reference,
        Collection<PackageInfo.ExecutionValueParameterSet> setValueParameters,
        Collection<PackageInfo.PropertyOverrideParameterSet> propertyOverrideParameters,
        int commandTimeout)
    {
        long executionId = 0;
        string connectionString = packageInfo.Parent.Parent.Parent.Parent.Connection.ServerConnection.ConnectionString;

        using (var connection = new SqlConnection(connectionString))
        {
            connection.Open();
            executionId = CreateExecution(packageInfo, use32RuntimeOn64,
                reference, commandTimeout, connection);

            SetExecutionParameterValues(packageInfo, setValueParameters,
                commandTimeout, connection, executionId);

            SetPropertyOverrideParameters(packageInfo, propertyOverrideParameters,
                commandTimeout, connection, executionId);

            StartExecution(commandTimeout, executionId, connection, packageInfo);
        }

        return executionId;
    }

    #endregion

    #region Private methods

    private static void StartExecution(int commandTimeout,
        long executionId, SqlConnection connection, PackageInfo packageInfo)
    {
        string storedProcName = string.Format("{0}.{1}",
        packageInfo.Parent.Parent.Parent.Name, START_EXECUTION);

        using (var command = new SqlCommand(storedProcName, connection))
        {
            command.CommandType = CommandType.StoredProcedure;
            command.CommandTimeout = commandTimeout;

            command.Parameters.Add(EXECUTION_ID, SqlDbType.Int);
            command.Parameters[EXECUTION_ID].Value = executionId;
            command.ExecuteNonQuery();
        }
    }

    private static long CreateExecution(PackageInfo packageInfo,
        bool use32RuntimeOn64, EnvironmentReference reference,
        int commandTimeout, SqlConnection connection)
    {
        long executionId = 0;
        string storedProcName = string.Format("{0}.{1}",
            packageInfo.Parent.Parent.Parent.Name, CREATE_EXECUTION);

        using (var command = new SqlCommand(storedProcName, connection))
        {
            command.CommandType = CommandType.StoredProcedure;
            command.CommandTimeout = commandTimeout;

            command.Parameters.Add(FOLDER_NAME, SqlDbType.NVarChar, 128);
            command.Parameters.Add(PROJECT_NAME, SqlDbType.NVarChar, 128);
            command.Parameters.Add(PACKAGE_NAME, SqlDbType.NVarChar, 260);
            command.Parameters.Add(REFERENCE_ID, SqlDbType.BigInt);
            command.Parameters.Add(USE32BITRUNTIME, SqlDbType.Bit);
            command.Parameters.Add(EXECUTION_ID, SqlDbType.Int);
            command.Parameters[EXECUTION_ID].Direction = ParameterDirection.Output;

            command.Parameters[FOLDER_NAME].Value = packageInfo.Parent.Parent.Name;
            command.Parameters[PROJECT_NAME].Value = packageInfo.Parent.Name;
            command.Parameters[PACKAGE_NAME].Value = packageInfo.Name;

            if (reference != null)
            {
                command.Parameters[REFERENCE_ID].Value = reference.ReferenceId;
            }

            command.Parameters[USE32BITRUNTIME].Value = use32RuntimeOn64;

            command.ExecuteNonQuery();
            executionId = long.Parse(command.Parameters[EXECUTION_ID].Value.ToString());
        }

        return executionId;
    }

    private static void SetExecutionParameterValues(PackageInfo packageInfo,
        Collection<PackageInfo.ExecutionValueParameterSet> setValueParameters,
        int commandTimeout, SqlConnection connection, long executionId)
    {
        if (setValueParameters != null && setValueParameters.Count > 0)
        {
            string storedProcName = string.Format("{0}.{1}",
                packageInfo.Parent.Parent.Parent.Name, SET_EXECUTION_PARAMETER_VALUE);

            using (var command = new SqlCommand(storedProcName, connection))
            {
                command.CommandType = CommandType.StoredProcedure;
                command.CommandTimeout = commandTimeout;

                command.Parameters.Add(EXECUTION_ID, SqlDbType.Int);
                command.Parameters.Add(OBJECT_TYPE, SqlDbType.SmallInt);
                command.Parameters.Add(PARAMETER_NAME, SqlDbType.NVarChar, 128);
                command.Parameters.Add(PARAMETER_VALUE, SqlDbType.Variant);
                command.Parameters[EXECUTION_ID].Value = executionId;

                foreach (var setValueParameter in setValueParameters)
                {
                    command.Parameters[OBJECT_TYPE].Value = setValueParameter.ObjectType;
                    command.Parameters[PARAMETER_NAME].Value = setValueParameter.ParameterName;
                    command.Parameters[PARAMETER_VALUE].Value = setValueParameter.ParameterValue;
                    command.ExecuteNonQuery();
                }
            }
        }
    }

    private static void SetPropertyOverrideParameters(PackageInfo packageInfo,
        Collection<PackageInfo.PropertyOverrideParameterSet> propertyOverrideParameters,
        int commandTimeout, SqlConnection connection, long executionId)
    {
        if (propertyOverrideParameters != null && propertyOverrideParameters.Count > 0)
        {
            string storedProcName = string.Format("{0}.{1}",
                packageInfo.Parent.Parent.Parent.Name, SET_EXECUTION_PROPERTY_OVERRIDE_VALUE);

            using (var command = new SqlCommand(storedProcName, connection))
            {
                command.CommandType = CommandType.StoredProcedure;
                command.CommandTimeout = commandTimeout;

                command.Parameters.Add(EXECUTION_ID, SqlDbType.Int);
                command.Parameters.Add(PROPERTY_PATH, SqlDbType.NVarChar, 4000);
                command.Parameters.Add(PROPERTY_VALUE, SqlDbType.NVarChar, -1);
                command.Parameters.Add(SENSITIVE, SqlDbType.Bit);
                command.Parameters[EXECUTION_ID].Value = executionId;

                foreach (var propertyOverrideParameter in propertyOverrideParameters)
                {
                    command.Parameters[PROPERTY_PATH].Value = propertyOverrideParameter.PropertyPath;
                    command.Parameters[PROPERTY_VALUE].Value = propertyOverrideParameter.PropertyValue;
                    command.Parameters[SENSITIVE].Value = propertyOverrideParameter.Sensitive;
                    command.ExecuteNonQuery();
                }
            }
        }
    }

    #endregion
}

Add the above class to your project (you can rename it to whatever you want) and now you can make the same call as before but you’ll be able to specify the timeout:

int timeOut = 60;
long executionIdentifier = package.Execute(false, null, setValueParameters, timeOut);

As with a normal SqlCommand execution, if you want no timeout then just supply a value of 0.

9 comments:

  1. Hey Ben,

    DO you have any exambles of using PackageInfo.PropertyOverrideParameterSet? I am trying to set connection strings dynamically using this method. There is a PropertyPath on the class and I am clueless as to what the format looks like. It keeps rejecting that value. If you have any insight that would be great.

    Thanks

    -Donald

    ReplyDelete
    Replies
    1. Donald not sure if you have figured it out. But to set parameter that are defined @ design time using the
      var package = integrationServices.Catalogs[""].Folders[""].Projects[""].Packages[""]; package.Parameters[""].Set(ParameterInfo.ParameterValueType.Literal, "");
      like so

      Delete
  2. Sorry, can't help you with that one. Never had cause to use it.

    ReplyDelete
  3. This is great. You've saved me a lot of work. I was running them from SQL Agent Jobs and trying to move them to programmatic execution, and many jobs worked, but then slightly longer jobs failed.

    ReplyDelete
  4. Hello Ben,
    thank you very much for your post, it helped me a lot. I have a question (not directly related):

    Do you know if it is necessary to specify variables that must be passed from a parent package to the child package. Intuitively I thought maybe one doesn't need to code it. But my test seems to show the contrary. Is there a way to pass the value of a variable from the parent package to its child. Thanks a lot for your response.

    ReplyDelete
    Replies
    1. I tend to use Environments for running packages so it's not a problem I've come up against...yet. Sorry I can't help you there.

      Delete
    2. Why not using Parameters instead? Should work the same if you take them into account in your package

      Delete
  5. Thank you very much. We just added your class to the project and it worked flawlessly from the first try!

    ReplyDelete
  6. Thank you very much for sharing your very well working extension class, you saved me a lot of tedious work!

    ReplyDelete