Partilhar via

Loading and Running a Remote Package Programmatically

  • Artigo

New: 14 April 2006

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.

Note

You cannot run packages outside BI Development Studio on a client computer that does not have Integration Services installed, and the terms of your SQL Server 2005 licensing might not let you install Integration Services on additional computers. SQL Server 2005 Integration Services (SSIS) is a server component and is not redistributable to client computers.

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.

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

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.

Important

With its default settings for authentication and authorization, a Web service generally does not have sufficient permissions to access SQL Server or the file system to load and execute packages. You may have to assign appropriate permissions to the Web service by configuring its authentication and authorization settings in the web.config file and assigning database and file system permissions as appropriate. A complete discussion of Web, database, and file system permissions is beyond the scope of this topic.

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)".

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

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

Help and Information

Getting SQL Server 2005 Assistance