Loading and Running a Local Package Programmatically

Mis à jour : 17 juillet 2006

You can run Integration Services packages as needed or at predetermined times by using the methods described in Exécution des packages. However, with only a few lines of code, you can also run a package from a custom application such as a Windows Forms application, a console application, an ASP.NET Web form or Web service, or a Windows service.

This topic discusses:

• Loading a package programmatically
• Running a package programmatically

All of the methods used in this topic to load and run packages require a reference to the Microsoft.SqlServer.ManagedDTS assembly. After adding the reference in a new project, import the Microsoft.SqlServer.Dts.Runtime namespace with a using or Imports statement.

Loading a Package Programmatically

To load a package programmatically on the local computer, whether the package is stored locally or remotely, call one of the following methods:

Storage Location	Method to Call
File	LoadPackage or LoadPackage
SSIS Package Store	LoadFromDtsServer
SQL Server	LoadFromSqlServer

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

Running a Package Programmatically

Developing a custom application in managed code that runs a package on the local computer requires the following approach. The steps summarized here are demonstrated in the sample console application that follows.

To run a package on the local computer programmatically

1. Start the Visual Studio development environment, and create a new application in your preferred development language. This example uses a console application; however you can also run a package from a Windows Forms application, an ASP.NET Web form or Web service, or a Windows service.

2. On the Project menu, click Add Reference and add a reference to Microsoft.SqlServer.ManagedDTS.dll. Click OK.

3. Use the Visual Basic Imports statement or the C# using statement to import the Microsoft.SqlServer.Dts.Runtime namespace.

4. Add the following code in the main routine. The completed console application should look like the following example.

   Remarque :
   The sample code demonstrates loading the package from the file system by using the LoadPackage method. However you can also load the package from the MSDB database by calling the LoadFromSqlServer method, or from the Integration Services package store by calling the LoadFromDtsServer method.

5. Run the project. The sample code executes the CalculatedColumns sample package that is installed with the SQL Server 2005 samples. The result of package execution is displayed in the console window.

Sample Code

Imports Microsoft.SqlServer.Dts.Runtime

Module Module1

  Sub Main()

    Dim pkgLocation As String
    Dim pkg As New Package
    Dim app As New Application
    Dim pkgResults As DTSExecResult

    pkgLocation = _
      "C:\Program Files\Microsoft SQL Server\90\Samples\Integration Services" & _
      "\Package Samples\CalculatedColumns Sample\CalculatedColumns\CalculatedColumns.dtsx"
    pkg = app.LoadPackage(pkgLocation, Nothing)
    pkgResults = pkg.Execute()

    Console.WriteLine(pkgResults.ToString())
    Console.ReadKey()

  End Sub

End Module

using System;
using Microsoft.SqlServer.Dts.Runtime;

namespace RunFromClientAppCS
{
  class Program
  {
    static void Main(string[] args)
    {
      string pkgLocation;
      Package pkg;
      Application app;
      DTSExecResult pkgResults;

      pkgLocation =
        @"C:\Program Files\Microsoft SQL Server\90\Samples\Integration Services" +
        @"\Package Samples\CalculatedColumns Sample\CalculatedColumns\CalculatedColumns.dtsx";
      app = new Application();
      pkg = app.LoadPackage(pkgLocation, null);
      pkgResults = pkg.Execute();

      Console.WriteLine(pkgResults.ToString());
      Console.ReadKey();
    }
  }
}

Capturing Events from a Running Package

When you run a package programmatically as shown in the preceding sample, you may also want to capture errors and other events that occur as the package executes. You can accomplish this by adding a class that inherits from the DefaultEvents class, and by passing a reference to that class when you load the package. Although the following example captures only the OnError event, there are many other events that the DefaultEvents class lets you capture.

To run a package on the local computer programmatically and capture package events

1. Follow the steps in the preceding example to create a project for this example.

2. Add the following code in the main routine. The completed console application should look like the following example.

3. Run the project. The sample code executes the CalculatedColumns sample package that is installed with the SQL Server 2005 samples. The result of package execution is displayed in the console window, along with any errors that occur.

Sample Code

Imports Microsoft.SqlServer.Dts.Runtime

Module Module1

  Sub Main()

    Dim pkgLocation As String
    Dim pkg As New Package
    Dim app As New Application
    Dim pkgResults As DTSExecResult

    Dim eventListener As New EventListener()

    pkgLocation = _
      "C:\Program Files\Microsoft SQL Server\90\Samples\Integration Services" & _
      "\Package Samples\CalculatedColumns Sample\CalculatedColumns\CalculatedColumns.dtsx"
    pkg = app.LoadPackage(pkgLocation, eventListener)
    pkgResults = pkg.Execute(Nothing, Nothing, eventListener, Nothing, Nothing)

    Console.WriteLine(pkgResults.ToString())
    Console.ReadKey()

  End Sub

End Module

Class EventListener
  Inherits DefaultEvents

  Public Overrides Function OnError(ByVal source As Microsoft.SqlServer.Dts.Runtime.DtsObject, _
    ByVal errorCode As Integer, ByVal subComponent As String, ByVal description As String, _
    ByVal helpFile As String, ByVal helpContext As Integer, _
    ByVal idofInterfaceWithError As String) As Boolean

    ' Add application–specific diagnostics here.
    Console.WriteLine("Error in {0}/{1} : {2}", source, subComponent, description)
    Return False

  End Function

End Class

using System;
using Microsoft.SqlServer.Dts.Runtime;

namespace RunFromClientAppWithEventsCS
{
  class MyEventListener : DefaultEvents
  {
    public override bool OnError(DtsObject source, int errorCode, string subComponent, 
      string description, string helpFile, int helpContext, string idofInterfaceWithError)
    {
      // Add application-specific diagnostics here.
      Console.WriteLine("Error in {0}/{1} : {2}", source, subComponent, description);
      return false;
    }
  }
  class Program
  {
    static void Main(string[] args)
    {
      string pkgLocation;
      Package pkg;
      Application app;
      DTSExecResult pkgResults;

      MyEventListener eventListener = new MyEventListener();

      pkgLocation =
        @"C:\Program Files\Microsoft SQL Server\90\Samples\Integration Services" +
        @"\Package Samples\CalculatedColumns Sample\CalculatedColumns\CalculatedColumns.dtsx";
      app = new Application();
      pkg = app.LoadPackage(pkgLocation, eventListener);
      pkgResults = pkg.Execute(null, null, eventListener, null, null);

      Console.WriteLine(pkgResults.ToString());
      Console.ReadKey();
    }
  }
}

Change History

Release	History
17 juillet 2006	Changed content: Added section about how to capture events, together with code sample.
14 avril 2006	Changed content: Showed the syntax for loading a package from SQL Server or the Integration Services package store in addition to the file system.
5 décembre 2005	Changed content: Added C# copy of sample. Added information about how to run packages from a client computer. Changed sample type from Windows Forms application to console application to simplify the sample.

Voir aussi

Tâches

Loading and Running a Remote Package Programmatically
Loading the Output of a Local Package

Concepts

Understanding the Differences between Local and Remote Execution

Aide et Informations

Assistance sur SQL Server 2005