Share via


Running an SSIS Package Programmatically on the Local Computer

With only a few lines of managed code and the correct prerequisites on the local computer, you can run a package from a custom application. The custom application can be a Windows Forms application, a console application, an ASP.NET Web form or Web service, or a Windows service.

Note

To run Integration Services packages as needed or at predetermined times, use the methods that are described in the topic, Running Packages.

Sections in This Topic

  • Prerequisites for the Local Computer

  • Creating an Application that Loads and Runs a Package on the Local Computer

    • Methods that Load a Package

    • Method that Runs the Package

    • Example

  • Capturing Events from a Running Package

Prerequisites for the Local Computer

A package runs on the same computer as the application that starts the package. Thus, when an application loads a package that is stored remotely on another computer, the package runs on the local computer.

As a result, loading and running a package on the local computer has several prerequisites:

  • If the package contains references to resources that are external to the package, those references must be valid on the local computer. These references include connection strings, servers, files, message queues, and so forth.

    Note

    If the local computer does not have valid references to the resources that are external to the package, you cannot run the package on the local computer. Instead, you must ensure that the package runs on the remote computer. For more information, see Running an SSIS Package Programmatically on a Remote Computer.

  • You must have all the permissions that the package requires to access data sources and other resources on or from the local computer.

  • You can only run a package outside the development environment, Business Intelligence Development Studio, on a computer that has Integration Services installed.

    Note

    The terms of your SQL Server licensing may not permit you to install Integration Services on additional computers. (Integration Services is a server component, and is not redistributable to client or local computers.) If Integration Services is not and cannot be installed on the local computer, you must ensure that the package runs on the remote computer. For more information, see Running an SSIS Package Programmatically on a Remote Computer.

Creating an Application that Loads and Runs an SSIS Package on the Local Computer

In this topic, all of the methods that load and run packages programmatically require a reference to the Microsoft.SqlServer.ManagedDTS assembly. After adding this reference in a new project, import the Microsoft.SqlServer.Dts.Runtime namespace with a C# using statement or a Visual Basic Imports statement.

Methods that Load an SSIS Package

To load a package programmatically on the local computer, regardless of whether the package is stored locally or remotely, call one of the methods in the following table. All of these methods return a Package object, which you can then use to run the package or to examine the package's properties.

Storage Location

Method to Call

File

LoadPackage

—or—

LoadPackage

SSIS Package Store

LoadFromDtsServer

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

SQL Server

LoadFromSqlServer

Method that Runs the SSIS Package

To run a package programmatically on the local computer, load the package as described in the previous section, and then call the Execute method of the package.

Example

Use the following procedure to develop a custom application in managed code that loads and runs a package on the local computer. Following the procedure is a sample console application that demonstrates the steps in the procedure.

To load and run a package programmatically on the local computer

  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. In the main routine, add the sample code that follows this procedure.

    The completed console application should look like the sample code in the following section.

    Note

    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 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\100\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\100\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 SSIS Package

When you run a package programmatically as shown in the sample in the previous section, you may also want to capture errors and other events that occur as the package runs. 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 example in the previous section to create a project for this example.

  2. In the main routine, add the sample code that appears after this procedure.

    The completed console application should look like the following example.

  3. Run the project.

    The sample code runs the CalculatedColumns sample package that is installed with the SQL Server 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\100\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\100\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();
    }
  }
}
Integration Services icon (small) Stay Up to Date with Integration Services

For the latest downloads, articles, samples, and videos from Microsoft, as well as selected solutions from the community, visit the Integration Services page on MSDN:


For automatic notification of these updates, subscribe to the RSS feeds available on the page.