Loading and Running a Remote Package Programmatically

To run remote packages from a local computer that does not have Integration Services installed, start the packages so that they run on the remote computer on which Integration Services is installed. You do this by having the local computer use SQL Server Agent, a Web service, or a remote component to start the packages on the remote computer. If you try to start the remote packages directly from the local computer, the packages will load onto and try to run from the local computer. If the local computer does not have Integration Services installed, the packages will not run.

Alternately, you can run a remote package from a local computer that has Integration Services installed. For more information, see Loading and Running a Local Package Programmatically.

Running a Remote Package on the Remote Computer

As mentioned above, there are multiple ways in which you can run a remote package on a remote server:

• Use SQL Server Agent to run the remote package programmatically

• Use a Web service or remote component to run the remote package programmatically

Almost all the methods that are used in this topic to load and save packages require a reference to the Microsoft.SqlServer.ManagedDTS assembly. The exception is the ADO.NET approach demonstrated in this topic for executing the sp_start_job stored procedure, which requires only a reference to System.Data. After you add the reference to the Microsoft.SqlServer.ManagedDTS assembly in a new project, import the Microsoft.SqlServer.Dts.Runtime namespace with a using or Imports statement.

Using SQL Server Agent to Run a Remote Package Programmatically on the Server

The following code sample demonstrates how to programmatically use SQL Server Agent to run a remote package on the server. The code sample calls the system stored procedure, sp_start_job, which launches a SQL Server Agent job. The job that the procedure launches is named RunSSISPackage, and this job is on the remote computer. The RunSSISPackage job then runs the package on the remote computer.

For information on troubleshooting packages that are run from SQL Server Agent jobs, see the Microsoft article, An SSIS package does not run when you call the SSIS package from a SQL Server Agent job step.

Sample Code

Imports System.Data
Imports System.Data.SqlClient

Module Module1

  Sub Main()

    Dim jobConnection As SqlConnection
    Dim jobCommand As SqlCommand
    Dim jobReturnValue As SqlParameter
    Dim jobParameter As SqlParameter
    Dim jobResult As Integer

    jobConnection = New SqlConnection("Data Source=(local);Initial Catalog=msdb;Integrated Security=SSPI")
    jobCommand = New SqlCommand("sp_start_job", jobConnection)
    jobCommand.CommandType = CommandType.StoredProcedure

    jobReturnValue = New SqlParameter("@RETURN_VALUE", SqlDbType.Int)
    jobReturnValue.Direction = ParameterDirection.ReturnValue
    jobCommand.Parameters.Add(jobReturnValue)

    jobParameter = New SqlParameter("@job_name", SqlDbType.VarChar)
    jobParameter.Direction = ParameterDirection.Input
    jobCommand.Parameters.Add(jobParameter)
    jobParameter.Value = "RunSSISPackage"

    jobConnection.Open()
    jobCommand.ExecuteNonQuery()
    jobResult = DirectCast(jobCommand.Parameters("@RETURN_VALUE").Value, Integer)
    jobConnection.Close()

    Select Case jobResult
      Case 0
        Console.WriteLine("SQL Server Agent job, RunSISSPackage, started successfully.")
      Case Else
        Console.WriteLine("SQL Server Agent job, RunSISSPackage, failed to start.")
    End Select
    Console.Read()

  End Sub

End Module

using System;
using System.Data;
using System.Data.SqlClient;

namespace LaunchSSISPackageAgent_CS
{
  class Program
  {
    static void Main(string[] args)
    {
      SqlConnection jobConnection;
      SqlCommand jobCommand;
      SqlParameter jobReturnValue;
      SqlParameter jobParameter;
      int jobResult;

      jobConnection = new SqlConnection("Data Source=(local);Initial Catalog=msdb;Integrated Security=SSPI");
      jobCommand = new SqlCommand("sp_start_job", jobConnection);
      jobCommand.CommandType = CommandType.StoredProcedure;

      jobReturnValue = new SqlParameter("@RETURN_VALUE", SqlDbType.Int);
      jobReturnValue.Direction = ParameterDirection.ReturnValue;
      jobCommand.Parameters.Add(jobReturnValue);

      jobParameter = new SqlParameter("@job_name", SqlDbType.VarChar);
      jobParameter.Direction = ParameterDirection.Input;
      jobCommand.Parameters.Add(jobParameter);
      jobParameter.Value = "RunSSISPackage";

      jobConnection.Open();
      jobCommand.ExecuteNonQuery();
      jobResult = (Int32)jobCommand.Parameters["@RETURN_VALUE"].Value;
      jobConnection.Close();

      switch (jobResult)
      {
        case 0:
          Console.WriteLine("SQL Server Agent job, RunSISSPackage, started successfully.");
          break;
        default:
          Console.WriteLine("SQL Server Agent job, RunSISSPackage, failed to start.");
          break;
      }
      Console.Read();
    }
  }
}

Back to top

Using a Web Service or Remote Component to Run a Remote Package Programmatically

The previous solution for running packages programmatically on the server does not require any custom code on the server. However, you may prefer a solution that does not rely on SQL Server Agent to execute packages. The following example demonstrates a Web service that can be created on the server to start Integration Services packages locally, and a test application that can be used to call the Web service from a client computer. If you prefer to create a remote component instead of a Web service, you can use the same code logic with very few changes in a remote component. However a remote component may require more extensive configuration than a Web service.

Sample Code

The following code samples show how to create and test the Web service.

Creating the Web Service

An Integration Services package can be loaded directly from a file, directly from SQL Server, or from the SSIS Package Store, which manages package storage in both SQL Server and special file system folders. This sample supports all the available options by using a Select Case or switch construct to select the appropriate syntax for starting the package and to concatenate the input arguments appropriately. The LaunchPackage Web service method returns the result of package execution as an integer instead of a DTSExecResult value so that client computers do not require a reference to any Integration Services assemblies.

To create a Web service to run packages on the server programmatically

1. Open Visual Studio and create a Web service project in your preferred programming language. The sample code uses the name LaunchSSISPackageService for the project.

2. Add a reference to Microsoft.SqlServer.ManagedDTS and add an Imports or using statement to the code file for the Microsoft.SqlServer.Dts.Runtime namespace.

3. Paste the sample code for the LaunchPackage Web service method into the class. (The sample shows the whole contents of the code window.)

4. Build and test the Web service by providing a set of valid values for the input arguments of the LaunchPackage method that point to an existing package. For example, if package1.dtsx is stored on the server in C:\My Packages, pass "file" as the value of the sourceType, "C:\My Packages" as the value of sourceLocation, and "package1" (without the extension) as the value of packageName.

Imports System.Web
Imports System.Web.Services
Imports System.Web.Services.Protocols
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.IO

<WebService(Namespace:="http://dtsue/")> _
<WebServiceBinding(ConformsTo:=WsiProfiles.BasicProfile1_1)> _
<Global.Microsoft.VisualBasic.CompilerServices.DesignerGenerated()> _
Public Class LaunchSSISPackageService
  Inherits System.Web.Services.WebService

  ' LaunchPackage Method Parameters:
  ' 1. sourceType: file, sql, dts
  ' 2. sourceLocation: file system folder, (none), logical folder
  ' 3. packageName: for file system, ".dtsx" extension is appended

  <WebMethod()> _
  Public Function LaunchPackage( _
    ByVal sourceType As String, _
    ByVal sourceLocation As String, _
    ByVal packageName As String) As Integer 'DTSExecResult

    Dim packagePath As String
    Dim myPackage As Package
    Dim integrationServices As New Application

    ' Combine path and file name.
    packagePath = Path.Combine(sourceLocation, packageName)

    Select Case sourceType
      Case "file"
        ' Package is stored as a file.
        ' Add extension if not present.
        If String.IsNullOrEmpty(Path.GetExtension(packagePath)) Then
          packagePath = String.Concat(packagePath, ".dtsx")
        End If
        If File.Exists(packagePath) Then
          myPackage = integrationServices.LoadPackage(packagePath, Nothing)
        Else
          Throw New ApplicationException( _
            "Invalid file location: " & packagePath)
        End If
      Case "sql"
        ' Package is stored in MSDB.
        ' Combine logical path and package name.
        If integrationServices.ExistsOnSqlServer(packagePath, ".", String.Empty, String.Empty) Then
          myPackage = integrationServices.LoadFromSqlServer( _
            packageName, "(local)", String.Empty, String.Empty, Nothing)
        Else
          Throw New ApplicationException( _
            "Invalid package name or location: " & packagePath)
        End If
      Case "dts"
        ' Package is managed by SSIS Package Store.
        ' Default logical paths are File System and MSDB.
        If integrationServices.ExistsOnDtsServer(packagePath, ".") Then
          myPackage = integrationServices.LoadFromDtsServer(packagePath, "localhost", Nothing)
        Else
          Throw New ApplicationException( _
            "Invalid package name or location: " & packagePath)
        End If
      Case Else
        Throw New ApplicationException( _
          "Invalid sourceType argument: valid values are 'file', 'sql', and 'dts'.")
    End Select

    Return myPackage.Execute()

  End Function

End Class

using System;
using System.Web;
using System.Web.Services;
using System.Web.Services.Protocols;
using Microsoft.SqlServer.Dts.Runtime;
using System.IO;

[WebService(Namespace = "http://dtsue/")]
[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
public class LaunchSSISPackageServiceCS : System.Web.Services.WebService
{
  public LaunchSSISPackageServiceCS()
  {
    }

  // LaunchPackage Method Parameters:
  // 1. sourceType: file, sql, dts
  // 2. sourceLocation: file system folder, (none), logical folder
  // 3. packageName: for file system, ".dtsx" extension is appended

  [WebMethod]
  public int LaunchPackage(string sourceType, string sourceLocation, string packageName)
  { 

    string packagePath;
    Package myPackage;
    Application integrationServices = new Application();

    // Combine path and file name.
    packagePath = Path.Combine(sourceLocation, packageName);

    switch(sourceType)
    {
      case "file":
        // Package is stored as a file.
        // Add extension if not present.
        if (String.IsNullOrEmpty(Path.GetExtension(packagePath)))
        {
          packagePath = String.Concat(packagePath, ".dtsx");
        }
        if (File.Exists(packagePath))
        {
          myPackage = integrationServices.LoadPackage(packagePath, null);
        }
        else
        {
          throw new ApplicationException("Invalid file location: "+packagePath);
        }
        break;
      case "sql":
        // Package is stored in MSDB.
        // Combine logical path and package name.
        if (integrationServices.ExistsOnSqlServer(packagePath, ".", String.Empty, String.Empty))
        {
          myPackage = integrationServices.LoadFromSqlServer(packageName, "(local)", String.Empty, String.Empty, null);
        }
        else
        {
          throw new ApplicationException("Invalid package name or location: "+packagePath);
        }
        break;
      case "dts":
        // Package is managed by SSIS Package Store.
        // Default logical paths are File System and MSDB.
        if (integrationServices.ExistsOnDtsServer(packagePath, "."))
        {
          myPackage = integrationServices.LoadFromDtsServer(packagePath, "localhost", null);
        }
        else
        {
          throw new ApplicationException("Invalid package name or location: "+packagePath);
        }
        break;
      default:
        throw new ApplicationException("Invalid sourceType argument: valid values are 'file', 'sql', and 'dts'.");
    }

    return (Int32)myPackage.Execute();

  }

}

Testing the Web Service

The following sample console application uses the Web service to run a package. The LaunchPackage method of the Web service returns the result of package execution as an integer instead of a DTSExecResult value so that client computers do not require a reference to any Integration Services assemblies. The sample creates a private enumeration whose values mirror the DTSExecResult values to report the results of execution.

To create a console application to test the Web service

1. In Visual Studio, add a new console application, using your preferred programming language, to the same solution that contains the Web service project. The sample code uses the name LaunchSSISPackageTest for the project.

2. Set the new console application as the startup project in the solution.

3. Add a Web reference for the Web service project. If necessary, adjust the variable declaration in the sample code for the name that you assign to the Web service proxy object.

4. Paste the sample code for the Main routine and the private enumeration into the code. (The sample shows the whole contents of the code window.)

5. Edit the line of code that calls the LaunchPackage method to provide a set of valid values for the input arguments that point to an existing package. For example, if package1.dtsx is stored on the server in C:\My Packages, pass "file" as the value of sourceType, "C:\My Packages" as the value of sourceLocation, and "package1" (without the extension) as the value of packageName.

Module LaunchSSISPackageTest

  Sub Main()

    Dim launchPackageService As New LaunchSSISPackageService.LaunchSSISPackageService
    Dim packageResult As Integer

    Try
      packageResult = launchPackageService.LaunchPackage("sql", String.Empty, "SimpleTestPackage")
    Catch ex As Exception
      ' The type of exception returned by a Web service is:
      '  System.Web.Services.Protocols.SoapException
      Console.WriteLine("The following exception occurred: " & ex.Message)
    End Try

    Console.WriteLine(CType(packageResult, PackageExecutionResult).ToString)
    Console.ReadKey()

  End Sub

  Private Enum PackageExecutionResult
    PackageSucceeded
    PackageFailed
    PackageCompleted
    PackageWasCancelled
  End Enum

End Module

using System;

namespace LaunchSSISPackageSvcTestCS
{
  class Program
  {
    static void Main(string[] args)
    {
      LaunchSSISPackageServiceCS.LaunchSSISPackageServiceCS launchPackageService = new LaunchSSISPackageServiceCS.LaunchSSISPackageServiceCS();
      int packageResult = 0;

      try
      {
        packageResult = launchPackageService.LaunchPackage("sql", String.Empty, "SimpleTestPackage");
      }
      catch (Exception ex)
      {
        // The type of exception returned by a Web service is:
        //  System.Web.Services.Protocols.SoapException
        Console.WriteLine("The following exception occurred: " + ex.Message);
      }

      Console.WriteLine(((PackageExecutionResult)packageResult).ToString());
      Console.ReadKey();

    }

    private enum PackageExecutionResult
    {
      PackageSucceeded,
      PackageFailed,
      PackageCompleted,
      PackageWasCancelled
    };

  }
}

Back to top

External Resources

• Video, How to: Automate SSIS Package Execution by Using the SQL Server Agent (SQL Server Video), on technet.microsoft.com

See Also

Tasks

Loading and Running a Local Package Programmatically

Loading the Output of a Local Package

Concepts

Understanding the Differences between Local and Remote Execution