Loading and Running a Remote Package Programmatically

Artikel
02/28/2023

Applies to: SQL Server SSIS Integration Runtime in Azure Data Factory

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 SQL Server Data Tools on a client computer that does not have Integration Services installed, and 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.

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

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

Open Visual Studio and create a Web service project in your preferred programming language. The sample code uses the name LaunchSSISPackageService for the project.
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.
Paste the sample code for the LaunchPackage Web service method into the class. (The sample shows the whole contents of the code window.)
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:="https://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 = "https://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

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.
Set the new console application as the startup project in the solution.
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.
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.)
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  
    };  
  
  }  
}

External Resources

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

Dela via

Loading and Running a Remote Package Programmatically

Running a Remote Package on the Remote Computer

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

Sample Code

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

Sample Code

Creating the Web Service

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

Testing the Web Service

To create a console application to test the Web service

External Resources

See Also

Feedback

Ytterligare resurser