Calling a SSIS Package from a Stored Procedure

Problem

Calling a SQL Server Integration Services (SSIS) package from s stored procedure should be an easy task however Microsoft has provided no direct method to do so.

A quick search on Google found this CodePlex example which uses the xp_cmdshell extended stored procedure to execute dtexec via the Windows Command Shell and this blog post (Rick Weyenberg) which describes how to use a stored procedure to create a SQL Server Job which executes a SSIS package via a Job Step.

Although the above examples work quite well, I wasn’t too keen on using xp_cmdshell and I wanted a solution that provided the ability to:

• Notify of execution failure
• Use dynamic package declaration
• Use dynamic variable assignment

Solution

This solution is based on the SQL Server Job example provided by Rick Weyenberg however it uses XML to implement my additional requirements.

It has been been tested on SQL Server 2005 – 2008(R2) and is configured to execute SSIS packages that reside on the same server as the executing stored procedure.

MSDN Gallery: http://code.msdn.microsoft.com/Calling-a-SSIS-Package-a35afefb

XML Structure

The structure of the XML used in this solution is as follows:

<package name="PackageName" notifyMethod="Method" notifyOperator="Operator" 
        owner="Owner" runAs="RunAs">
    <variable name="Variable1" value="Value"/>
    <variable name="Variable2" value="Value"/>
</package>

Package Element

• name: Name of the SSIS package to execute.  You need to specify the full package name and path i.e. if a package called SSISTest resides in a folder called Test within MSDB, the package name would be \Test\SSISTest
• notifyMethod: The method used to notify of job failure.  The value for this attribute corresponds with SQL Server Agent notification types (Except for “Automatically Delete Job” which is always set to “When the job completes” and “Write to the Windows Application Event Log” which is always set to “When the job fails”) .  Leave blank for no notification.
• notifyOperator: The name of the SQL Server Operator to notify when notifyMethod is provided.
• owner: An account in SQL Server that has sufficient permissions to execute jobs
• runAs: An proxy account that has sufficient permissions to execute any tasks within the SSIS package.  Leave blank to run under the SQL Server Agent Service Account.

I have only added the owner and runAs attributes to demonstrate that this can be done.  I recommend that you do not allow the caller of the stored procedure to nominate which accounts to use.

Variable Element

• name: Name of the SSIS variable you want to assign a value to
• value: Value you want to assign to the variable

Stored Procedure

The stored procedure used to implement this solution is quite lengthy so I’ll break it down into chunks and provide explanations where required.

1) Create the stored procedure shell which accepts a parameter of type XML and implements some basic error handling.

The error handling implemented here only relates to job creation. Any errors that occur during job execution will be logged in the Windows Application Event Log.

CREATE PROCEDURE [dbo].[usp_ExecuteSSISPackage]
(
  @xVar XML
)
AS
  BEGIN
    -- Step 2 code goes here

    BEGIN TRY
      -- Step 3 code goes here
      
      -- Step 4 code goes here

      -- Step 5 code goes here

      -- Step 6 code goes here

      -- Step 7 code goes here

      -- Return successfull job creation
      RETURN 1
    END TRY
    BEGIN CATCH
      -- Retrieve error details
      SELECT ERROR_NUMBER() AS ErrorNumber,ERROR_MESSAGE() AS ErrorMessage;

      -- Return failed job creation
      RETURN 0
    END CATCH   
  END

2) Create some local variables.

@VariableTable is used to hold the shredded XML variable data (@xVar parameter)

    -- Job specific variables
    DECLARE @Server NVARCHAR(100)
    DECLARE @JobName NVARCHAR(128)
    DECLARE @JobId BINARY(16)
    DECLARE @StepCmd VARCHAR(MAX)

    -- Used to create SSIS variable mappings
    DECLARE @MaxID BIGINT
    DECLARE @CurrentID BIGINT
    DECLARE @VariableList VARCHAR(MAX)
    DECLARE @VariableTable TABLE 
      (
        ID BIGINT IDENTITY(1,1),
        Name VARCHAR(50),
        Value VARCHAR(255)
      )

    -- Attribute Variables
    DECLARE @PackageName VARCHAR(255)
    DECLARE @NotifyMethod VARCHAR(10)
    DECLARE @NotifyOperator VARCHAR(255)
    -- ***********************************************
    -- Please Note: I have only added the owner
    -- and runAs attributes to demonstrate that this 
    -- can be done.  I recommend that you DO NOT allow 
    -- the caller of the stored procedure to assign 
    -- which accounts to use. 
    DECLARE @Owner VARCHAR(100)
    DECLARE @RunAsAccount VARCHAR(100)
    -- ***********************************************

3) Shred the XML contained in the @xVar parameter and store the resulting data in local variables

      -- Get package attributes
      SELECT @PackageName = nref.value('@name[1]','varchar(255)'),
             @NotifyMethod = nref.value('@notifyMethod[1]', 'varchar(10)'),
             @NotifyOperator = nref.value('@notifyOperator[1]', 'varchar(255)'),
             @Owner = nref.value('@owner[1]','varchar(100)'),
             @RunAsAccount = nref.value('@runAs[1]', 'varchar(100)')
      FROM   @xVar.nodes('/package') AS R(nref)

      -- Store SSIS variable details in local table variable
      INSERT INTO @VariableTable
      SELECT nref.value('@name[1]', 'varchar(50)'),
             nref.value('@value[1]', 'varchar(50)')
      FROM   @xVar.nodes('/package/variable') AS R(nref)

4) Create the job.

The job name (@JobName) is made up of the combination of the package name and a GUID.  This enables multiple copies of the same package to be executed at the same time.

@delete_level = 3 causes the job to be deleted regardless of whether job execution succeeded

      -- Get the current server name
      SET @Server = CONVERT(sysname, SERVERPROPERTY(N'servername'))

      -- Create a unique job name 
      SET @JobName = @PackageName + '_' + CAST(NEWID() AS CHAR(36))

      -- Create the job
      IF @NotifyMethod = 'E-mail'
        BEGIN
          EXEC  msdb.dbo.sp_add_job @job_name = @JobName, @enabled = 1, 
            @notify_level_email = 2, -- When the job fails
            @notify_email_operator_name = @NotifyOperator, 
            @notify_level_eventlog = 2, -- When the job fails
            @Owner_login_name = @Owner,
            @delete_level = 3, -- Delete job regardless of execution state
            @job_id = @JobId OUTPUT
        END
      ELSE IF @NotifyMethod = 'Page'
        BEGIN
          EXEC  msdb.dbo.sp_add_job @job_name = @JobName, @enabled = 1, 
            @notify_level_page = 2, -- When the job fails
            @notify_page_operator_name = @NotifyOperator, 
            @notify_level_eventlog = 2, -- When the job fails 
            @Owner_login_name = @Owner,
            @delete_level = 3, -- Delete job regardless of executionstate,
            @job_id = @JobId OUTPUT
        END
      ELSE IF @NotifyMethod = 'Net send'
        BEGIN
          EXEC  msdb.dbo.sp_add_job @job_name = @JobName, @enabled = 1, 
            @notify_level_netsend = 2, -- When the job fails 
            @notify_netsend_operator_name = @NotifyOperator, 
            @notify_level_eventlog = 2, -- When the job fails
            @Owner_login_name = @Owner,
            @delete_level = 3, -- Delete job regardless of execution state, 
            @job_id = @JobId OUTPUT
        END
      ELSE 
        BEGIN
          EXEC  msdb.dbo.sp_add_job @job_name = @JobName, @enabled = 1, 
            @notify_level_eventlog = 2, -- When the job fails
            @Owner_login_name = @Owner,
            @delete_level = 3, -- Delete job regardless of execution state, 
            @job_id = @JobId OUTPUT
        END
      
      -- Add the job to the server
      EXEC msdb.dbo.sp_add_jobserver @job_name = @JobName, @server_name = @Server

5) Get the package variables and their associated values.

This code portion loops through the rows in the @VariableTable and builds a string which is used to add the variables and their associated values to the job step.  The syntax used to set package variables is:

/SET \Package.Variables[User::VariableName].Value;VariableValue

e.g.

/SET \Package.Variables[User::ProjectID].Value;666

Actual stored procedure code.

      -- Build the SSIS variable assignment list
      SELECT @MaxID = MAX(ID) FROM @VariableTable
      SET @VariableList = ''
      SET @CurrentID = 0      
      WHILE (@CurrentID <= @MaxID)
        BEGIN
          SELECT @VariableList = @VariableList 
            + ' /SET \Package.Variables[User::' + Name + '].Value;' + Value 
          FROM @VariableTable
          WHERE ID = @CurrentID

          SET @CurrentID = @CurrentID + 1
        END

6) Create the job step

        -- Create the job step
        SET @StepCmd = '/SQL "' + @PackageName + '" /SERVER ' 
          + @Server + ' /CHECKPOINTING OFF ' + @VariableList + ' /REPORTING E'

        EXEC msdb.dbo.sp_add_jobstep @job_name = @JobName, 
          @step_name = N'SSISExecuteStep', @step_id = 1, 
          @cmdexec_success_code = 0, @on_success_action = 1, 
          @on_fail_action = 2, @retry_attempts = 0, 
          @retry_interval = 0, @os_run_priority = 0, 
          @subsystem = N'SSIS', @command = @StepCmd, 
          @database_name = N'master', @flags=0, @proxy_name=@RunAsAccount

7) Start the job

        -- Start job
        EXEC msdb.dbo.sp_start_job @job_id = @JobId

That’s it.  To execute the stored procedure in SSMS use:

DECLARE @XML XML
SET @XML = 
'<package name="\SSISTest\SSISTest" notifyMethod="E-mail" notifyOperator="ITSupport" 
        owner="Machine\Administrator" runAs="">
    <variable name="TestID" value="2"/>
    <variable name="ModifiedBy" value="Jeff"/>
</package>'

exec [dbo].[usp_ExecuteSSISPackage] @XML

To call the stored procedure from .Net is very straight forward.

I’m using the Microsoft Enterprise Library 5.0 as it handles the majority of the database code required to connect to SQL Server and execute the stored procedure.

using Microsoft.Practices.EnterpriseLibrary.Data;
using Microsoft.Practices.EnterpriseLibrary.Data.Sql;
using System.Data.Common;
using System;
using System.Data;

class SSIS
{
    public int ExecuteSSISPackage()
    {
        SqlDatabase db = (SqlDatabase)DatabaseFactory.CreateDatabase();

        // This is the stored procedure we created earlier
        DbCommand dbCommand = 
            db.GetStoredProcCommand("[dbo].[usp_ExecuteSSISPackage]");

        // Add a paramter of type XML and assign the 
        String xmlData = "<Your XML Data Goes Here>";

        db.AddInParameter(dbCommand, "@xVar", DbType.Xml, xmlData);
        // Add a ReturnValue paramter of type INT for error checking
        db.AddParameter(dbCommand, "@RETURN_VALUE", DbType.Int16, 0, 
            ParameterDirection.ReturnValue, false, 0, 0, "", 
            DataRowVersion.Current, 0);
            
        // Execute the stored procedure
        DataSet ds = db.ExecuteQuery(dbCommand);
            
        //Get error indicator
        int erroCode = 
            Convert.ToInt32(db.GetParameterValue(dbCommand, "@RETURN_VALUE"));

        return erroCode;
    }
}

Conclusion

In this post I have demonstrated how to call a SSIS package from a SQL Server stored procedure.

Shortly I will demonstrate how to achieve the same result in Denali.  In the meantime, have a read of the following post by Jamie Thomson (SSIS Junkie) – Synchronous execution of SSIS packages using T-SQL : Denali