Enumerating Available Packages Programmatically

New: 14 April 2006

As you work programmatically with Integration Services packages, you may want to determine whether an individual package or folder exists, or to enumerate the saved packages that are available to load and execute. The Application class of the Microsoft.SqlServer.Dts.Runtime namespace provides a variety of methods to satisfy these requirements.

For information about loading and running packages programmatically, see Loading and Running a Local Package Programmatically and Loading and Running a Remote Package Programmatically. For information about managing packages and folders programmatically, see Managing Packages and Folders Programmatically.

All the methods discussed in this topic 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.

ms403343.note(en-US,SQL.90).gifImportant:
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)".

To determine programmatically whether a saved package exists, call one of the following methods before attempting to load and run it:

Storage Location Method to Call

SSIS Package Store

ExistsOnDtsServer

SQL Server

ExistsOnSqlServer

To determine programmatically whether a folder exists before attempting to list the packages stored in it, call one of the following methods:

Storage Location Method to Call

SSIS Package Store

FolderExistsOnDtsServer

SQL Server

FolderExistsOnSqlServer

Back to top

To obtain a list of saved packages programmatically, call one of the following methods:

Storage Location Method to Call

SSIS Package Store

GetDtsServerPackageInfos

SQL Server

GetPackageInfos

The following samples are console applications that demonstrate the use of these methods.

Example (SSIS Package Store)

Use the GetDtsServerPackageInfos method to list packages stored in the SSIS Package Store. The default storage locations that are managed by the SSIS Package store are File System and MSDB. You can create additional logical folders within these locations.

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

namespace EnumeratePackagesSSIS_CS
{
  class Program
  {
    static void Main(string[] args)
    {

      string sqlFolder;
      string sqlServer;

      Application ssisApplication;
      PackageInfos sqlPackages;

      sqlServer = ".";

      ssisApplication = new Application();

      // Get packages stored in MSDB.
      sqlFolder = "MSDB";
      sqlPackages = ssisApplication.GetDtsServerPackageInfos(sqlFolder, sqlServer);
      if (sqlPackages.Count > 0)
      {
        Console.WriteLine("Packages stored in MSDB:");
        foreach (PackageInfo sqlPackage in sqlPackages)
        {
          Console.WriteLine(sqlPackage.Name);
        }
        Console.WriteLine();
      }

      // Get packages stored in the File System.
      sqlFolder = "File System";
      sqlPackages = ssisApplication.GetDtsServerPackageInfos(sqlFolder, sqlServer);
      if (sqlPackages.Count > 0)
      {
        Console.WriteLine("Packages stored in the File System:");
        foreach (PackageInfo sqlPackage in sqlPackages)
        {
          Console.WriteLine(sqlPackage.Name);
        }
      }

      Console.Read();

    }

  }

}

Back to top

Example (SQL Server)

Use the GetPackageInfos method to list Integration Services packages that are stored in an instance of SQL Server 2005.

Visual Basic
Imports Microsoft.SqlServer.Dts.Runtime

Module Module1

  Sub Main()

    Dim sqlFolder As String
    Dim sqlServer As String
    Dim sqlUser As String
    Dim sqlPassword As String

    Dim ssisApplication As Application
    Dim sqlPackages As PackageInfos
    Dim sqlPackage As PackageInfo

    sqlFolder = String.Empty
    sqlServer = "(local)"
    sqlUser = String.Empty
    sqlPassword = String.Empty

    ssisApplication = New Application()

    sqlPackages = ssisApplication.GetPackageInfos(sqlFolder, sqlServer, sqlUser, sqlPassword)

    For Each sqlPackage In sqlPackages
      Console.WriteLine(sqlPackage.Name)
    Next

    Console.Read()

  End Sub

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

namespace EnumeratePackagesSql_CS
{
  class Program
  {
    static void Main(string[] args)
    {

      string sqlFolder;
      string sqlServer;
      string sqlUser;
      string sqlPassword;

      Application ssisApplication;
      PackageInfos sqlPackages;

      sqlFolder = String.Empty;
      sqlServer = "(local)";
      sqlUser = String.Empty;
      sqlPassword = String.Empty;

      ssisApplication = new Application();

      sqlPackages = ssisApplication.GetPackageInfos(sqlFolder, sqlServer, sqlUser, sqlPassword);

      foreach (PackageInfo sqlPackage in sqlPackages)
      {
        Console.WriteLine(sqlPackage.Name);
      }

      Console.Read();

    }
  }
}

Back to top

Community Additions

ADD
Show: