Loading and Running a Remote Package Programmatically

An Integration Services pakiet can be loaded directly from a file, directly from SQL Server, or from the SSIS pakiet Store, which manages pakiet storage in both SQL Server and special file system folders. W tym przykładzie obsługuje wszystkie dostępne opcje, korzystając z Select Case lub switch konstrukcja, aby wybrać odpowiednią składnię do uruchomienia pakiet i aby złączyć argumenty wejściowe odpowiednio. metoda usługa sieci LaunchPackage Web zwraca wynik uruchomienia pakiet jako liczba całkowita zamiast DTSExecResult wartość tak, aby klient komputerów nie jest wymagane odwołanie do dowolnej Integration Services zespoły.

Aby utworzyć usługa programowo uruchomienie pakietów na serwerze sieci Web

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

  }

}

Uruchomienie pakiet następującej przykładowej aplikacji konsoli korzystają z usługa sieci Web.Metoda LaunchPackage usługa sieci Web zwraca wynik uruchomienia pakiet jako liczba całkowita zamiast DTSExecResult wartość tak, aby komputery klienckie nie wymagają odwołania do wszelkich Integration Services zespoły. Próbka tworzy prywatnych wyliczenia którego dublowanie wartości DTSExecResult wartości, które mają raportować wyniki jego działania.

Aby utworzyć aplikację konsoli do testowania usługa sieci 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
    };

  }
}

Powrót do początku