Enumerating Available Packages Programmatically

 

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 T:Microsoft.SqlServer.Dts.Runtime.Application class of the N:Microsoft.SqlServer.Dts.Runtime namespace provides a variety of methods to satisfy these requirements.

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

Storage LocationMethod to Call
SSIS Package StoreM:Microsoft.SqlServer.Dts.Runtime.Application.ExistsOnDtsServer(System.String,System.String)
SQL ServerM:Microsoft.SqlServer.Dts.Runtime.Application.ExistsOnSqlServer(System.String,System.String,System.String,System.String)

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

Storage LocationMethod to Call
SSIS Package StoreM:Microsoft.SqlServer.Dts.Runtime.Application.FolderExistsOnDtsServer(System.String,System.String)
SQL ServerM:Microsoft.SqlServer.Dts.Runtime.Application.FolderExistsOnSqlServer(System.String,System.String,System.String,System.String)

Back to top

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

Storage LocationMethod to Call
SSIS Package StoreM:Microsoft.SqlServer.Dts.Runtime.Application.GetDtsServerPackageInfos(System.String,System.String)
SQL ServerM:Microsoft.SqlServer.Dts.Runtime.Application.GetPackageInfos(System.String,System.String,System.String,System.String)

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

Example (SSIS Package Store)

Use the M:Microsoft.SqlServer.Dts.Runtime.Application.GetDtsServerPackageInfos(System.String,System.String) 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 M:Microsoft.SqlServer.Dts.Runtime.Application.GetPackageInfos(System.String,System.String,System.String,System.String) method to list Integration Services packages that are stored in an instance of SQL Server.

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

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:

- Visit the Integration Services page on MSDN

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

Package Management (SSIS Service)

Show: