Running an SSIS Package Programmatically on a Remote Computer

If a client (local) computer either does not have Integration Services installed or does not have access to all the resources that a package requires, you must start the package so that the package runs on the remote computer where the package is stored. You must take extra steps to ensure that the package runs on the remote computer, because a package runs on the same computer as the application that starts the package. Thus, if you run a remote package directly from an application on the local computer, the package will load and run from the local computer. Because the local computer either does not have Integration Services installed, or does not have access to all the resources that the package requires, the package will not run successfully.

Note

A client computer might not have Integration Services installed because the terms of your SQL Server licensing might not let you install Integration Services on additional computers. (Integration Services is a server component and is not redistributable to client computers.) However, if the local computer has Integration Services installed and has access to all the resources that the package requires, you can use the local computer to run a remote package. For more information, see Running an SSIS Package Programmatically on the Local Computer.

To start the package on the remote computer, you call one of the following programs:

• SQL Server Agent.

• Another application, component, or Web service that is running on the remote computer.

Sections in This Topic Prerequisites for the Remote Computer Running a package remotely by calling SQL Server Agent Running a package remotely by calling a remote component or service

Prerequisites for the Remote Computer

You have to ensure that the process that runs the package on the remote computer has the required permissions. This process requires permission not only to start the package, but to find and open all the resources that the package uses. The default permissions are frequently not sufficient, especially in a Web-based solution. A full discussion of permissions, and of authentication and authorization, is beyond the scope of this topic.

Impersonating another account to run the package is often not a successful solution. Although the package starts under the impersonated account, the additional threads that are created by the package revert to the account that is used by the process that started the package. These threads include the threads used by the data flow to load and save data. Therefore, the process account itself needs permission to most of the external resources used by the package.

Running an SSIS Package Remotely by Calling SQL Server Agent

The following sample code demonstrates how to call SQL Server Agent programmatically to run a package on the remote computer. The sample code calls the system stored procedure, sp_start_job, which in turn launches a SQL Server Agent job that is named, RunSSISPackage, and is on the remote computer. The RunSSISPackage job then runs the package on the remote computer where the job itself is running.

Note

The return value of the sp_start_job stored procedure indicates whether the stored procedure was able to start the SQL Server Agent job successfully. The return value does not indicate whether the package succeeded or failed.

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

The following code samples require a reference to the System.Data assembly.

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

Running an SSIS Package Remotely by Calling a Remote Component or Service

In the previous section, the solution for running packages programmatically on a remote computer does not require any custom code on the remote computer. However, you may prefer a solution that does not rely on SQL Server Agent to run packages.

The following example provides partial code that could be used in a remote component or a Web service on the server to start Integration Services packages on the remote computer.

Sample Code

The following code samples suggest an approach for creating and testing a remote class that runs a package remotely. These samples are not complete, but provide code that could be used as part of a solution built as a remote component or as a Web service.

The samples require a reference to the Microsoft.SqlServer.ManagedDTS assembly.

Creating the Function to Run the SSIS Package Remotely

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. To support all the available loading options, this sample uses a Select Case or switch construct to select the appropriate syntax for starting the package and to concatenate the input arguments appropriately.

Important

The methods of the Application class for working with the SSIS Package Store support only ".", localhost, or the server name for the local server. You cannot use "(local)".

The LaunchPackage 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.

Important

This sample is not complete, but provides code that could be used as part of a solution built as a remote component or as a Web service. The sample does not include the code, attributes, or references that may be required to build and expose a remote component or service.

To create a remote class to run packages on the server programmatically

1. Open Visual Studio and create a project of the appropriate type in your preferred programming language.

   The following sample code uses the name LaunchSSISPackageService for the project.

2. Add a reference to the Microsoft.SqlServer.ManagedDTS assembly.

3. Paste the sample code into the class file.

   The sample shows the whole contents of the code window.

4. Add the code, attributes, or references that may be required to build and expose a remote component or service.

5. Build and test the project that includes the sample code.

Imports Microsoft.SqlServer.Dts.Runtime
Imports System.IO

Public Class LaunchSSISPackageService

  ' 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

  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 Microsoft.SqlServer.Dts.Runtime;
using System.IO;

public class LaunchSSISPackageServiceCS
{
  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

  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();

  }

}

Calling the Function to Run the SSIS Package Remotely

The following sample console application uses the remote component or service to run a package.

The LaunchPackage method of the remote class 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 remote class

1. In Visual Studio, create a new console application using your preferred programming language.

   The sample code uses the name LaunchSSISPackageTest for the project.

2. Add a reference to the local proxy assembly that represents the remote component or service. If necessary, adjust the variable declaration in the sample code for the name that you assign to the proxy object.

3. 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.

4. 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
      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)
      {
        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

Stay Up to Date with Integration Services For the latest downloads, articles, samples, and videos from Microsoft, as well as selected solutions from the community, visit the Integration Services page on MSDN: Visit the Integration Services page on MSDN For automatic notification of these updates, subscribe to the RSS feeds available on the page.

See Also

Tasks

Running an SSIS Package Programmatically on the Local Computer

Loading the Output of a Package into Another Program