Chargement et exécution d'un package distant par programme

Un package Integration Services peut être chargé directement à partir d'un fichier, directement à partir de SQL Server ou à partir du magasin de packages SSIS, lequel gère le stockage des packages dans SQL Server et des dossiers spéciaux du système de fichiers. Cet exemple prend en charge toutes les options disponibles en utilisant une construction Select Case ou switch pour sélectionner la syntaxe appropriée au démarrage du package et pour concaténer les arguments d'entrée convenablement. La méthode de service Web LaunchPackage renvoie le résultat de l'exécution du package sous la forme d'un entier plutôt que d'une valeur DTSExecResult afin que les ordinateurs clients ne requièrent pas de référence aux assemblys Integration Services.

Pour créer un service Web afin d'exécuter par programme des packages sur le serveur

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

  }

}

L'exemple d'application console suivant utilise le service Web pour exécuter un package. La méthode LaunchPackage du service Web renvoie le résultat de l'exécution du package sous la forme d'un entier plutôt que d'une valeur DTSExecResult afin que les ordinateurs clients ne requièrent pas de référence aux assemblys Integration Services. L'exemple crée une énumération privée dont les valeurs reflètent les valeurs DTSExecResult pour rapporter les résultats de l'exécution.

Pour créer une application console afin de tester le service Web

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

  }
}

Retour en haut