Share via

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

In This Topic

Determining whether a package or folder exists
Enumerating available packages
- Example (SSIS Package Store)
- Example (SQL Server)

For information about loading and running packages programmatically, see Running an SSIS Package Programmatically on the Local Computer and Running an SSIS Package Programmatically on a Remote Computer. 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.

Important

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

Determining Whether a Package or Folder Exists

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

Enumerating Available Packages

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.

Imports Microsoft.SqlServer.Dts.Runtime
Module Module1
 Sub Main()
 Dim sqlFolder As String
 Dim sqlServer As String
 Dim ssisApplication As Application
 Dim sqlPackages As PackageInfos
 Dim sqlPackage As PackageInfo
 sqlServer = "."
 ssisApplication = New Application()
 ' Get packages stored in MSDB.
 sqlFolder = "MSDB"
 sqlPackages = ssisApplication.GetDtsServerPackageInfos(sqlFolder, sqlServer)
 If sqlPackages.Count > 0 Then
 Console.WriteLine("Packages stored in MSDB:")
 For Each sqlPackage In sqlPackages
 Console.WriteLine(sqlPackage.Name)
 Next
 Console.WriteLine()
 End If
 ' Get packages stored in the File System.
 sqlFolder = "File System"
 sqlPackages = ssisApplication.GetDtsServerPackageInfos(sqlFolder, sqlServer)
 If sqlPackages.Count > 0 Then
 Console.WriteLine("Packages stored in the File System:")
 For Each sqlPackage In sqlPackages
 Console.WriteLine(sqlPackage.Name)
 Next
 End If
 Console.Read()
 End Sub
End Module

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

Example (SQL Server)

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

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

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 or TechNet:

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