Application Object Maintenance Operations-SSIS

  • (4.0)

SSIS packages can be stored on either the Windows file system or the SSIS catalog. For pre-2012 SQL Server instances and SSIS 2014 projects using the package deployment model, packages can also be stored in the SSIS package store or within SQL Server. The methods of the Application object enable you to manage SSIS packages in each of these storage scenarios excluding the SSIS catalog, including management of packages in other server instances. Once the package is loaded or constructed in the Application object, itx may be run or executed. The flexibility to store, load, and run packages in separate machine spaces expands the scaling capabilities of SSIS packages.

Learn how to use SSIS, from beginner basics to advanced techniques, with online video tutorials taught by industry experts. Enroll for Free SSIS Training Demo!

The convention that the SSIS team chose to employ in naming the methods on this Application class is to use DtsServer in their names when the operation applies to packages in the SSIS package store and SqlServer in their names when storage is in SQL Server. If you don’t see either of these in the method name, typically this means that the operation is for packages stored in the file system.

In terms of the operations that the Application object supports, you’ll find methods for general package, folder, and role maintenance.

Package Operations

The Application object exposes the following methods to manage packages in the Windows file system, the SSIS package store, and the SQL Server database instance. The SSIS package store has been replaced by the SSIS catalog, so any of the LoadFrom… and SaveTo… methods (with exception of SaveToXML) are now obsolete if you’re using the SSIS catalog. These methods can still be used if you prefer to use the pre-SQL Server 2012 storage methods, which are still supported in SQL Server 2014.

  • LoadPackage: Loads a package from the file system
  • LoadFromDtsServer: Loads a package from the specified SSIS package store
  • LoadFromSqlServer: Loads a package from the specified SQL Server instance
  • LoadFromSqlServer2: Loads a package from the specified SQL Server instance by supplying a valid connection object
  • SaveToXML: Saves a package object to the file system with a dtsx file extension
  • SaveToDtsServer: Saves a package to the SSIS package store
  • SaveToSqlServer: Saves a package to the specified SQL Server instance
  • SaveToSqlServerAs: Saves a package as a different name to the specified SQL Server instance
  • RemoveFromDtsServer: Removes a package from the SSIS package store
  • RemoveFromSqlServer: Removes a package from the specified SQL Server instance
  • ExistsOnDtsServer: Indicates whether a specified package already exists in the SSIS package store at the specified path
  • ExistsOnSqlServer: Indicates whether a specified package already exists on a specified SQL Server

Now armed with the basic capabilities of the Application class, you can put together some real-world examples. First you’ll put together an example that examines the variables in a package, and then you’ll look at how you can programmatically deploy packages to a DTS package store.

A Package Maintenance Example

At the most basic level, you need to understand how to access a package programmatically to examine its internals. This is where the Package object class is used. This class mirrors the structure of the SSIS packages and allows them to be loaded into a navigable object model. Once the package is deep copied into this Package structure, you can look at anything in the package. The following C# code snippet is just a partial example of how to load your demonstration package into a Package object variable from the file system. Notice that because the package exists on the file system, you are using the LoadPackage method of the Application object instead of the methods that apply to DTS or SQL Server package stores. Note that this snippet assumes you have the references to Microsoft.SQLServer.ManagedDTS.dll. Code can be found in the ExtMgt folder in the CSharp_LoadPackage or vb_LoadPackage.

using Microsoft.SqlServer.Dts.Runtime;
public void LoadPackage()
pplication dtsApp = new Application();
string TestPackageFullPath =
Package pac = dtsApp.LoadPackage(TestPackageFullPath, null);

Imports Microsoft.SqlServer.Dts.Runtime
Public Sub LoadPackage()
Dim dtsApp as new Application()
Dim TestPackageFullPath as String = _
Dim package As Package = dtsApp.LoadPackage(TestPackageFullPath,

End Sub

Once the Application class is created and the Package object is loaded, you can interrogate the Package object to perform many different tasks. A useful example involves examining the variables within a package. You can do this easily by iterating the variables collection to look for user variables or system variables. The following code snippet (from the ExtMgt folder in CSharp_LoadPackage or vb_LoadPackage) is in the form of a function that you can add to your solution to perform this task:

private static void DisplayFilePackageVariables(string FullPath,
bool ShowOnlyUserVariables)
Application app = new Application()
Package package = app.LoadPackage(FullPath, null);
string sMsg = “Variable:[{0}] Type:{1} Default Value:{2}
foreach(Variable var in package.Variables)
if ((var.Namespace != “System”) &&
ShowOnlyUserVariables) ||
Console.WriteLine(String.Format(sMsg, var.Name,
Private Sub DisplayFilePackageVariables(ByVal FullPath As String,
ShowOnlyUserVariables As Boolean)
Dim app As New Application()
Dim package As Package = app.LoadPackage(FullPath, Nothing)
Dim sMsg As String
sMsg = “Variable:[{0}] Type:{1} Default Value:{2} IsExpression?:{3}”
+ vbCrLf
For Each Variable In package.Variables
If ((Variable.Namespace <> “System” And _
ShowOnlyUserVariables = True) Or _
ShowOnlyUserVariables = False) Then
Console.WriteLine(String.Format(sMsg, Variable.Name, _
Variable.DataType.ToString(), _
Variable.Value.ToString(), _
End If
End Sub

If you run this code using the full file path of the demonstration SSIS package and set the option to show only the user variables, the console results will look like below screen shot.

You can see in the console that this correctly shows the two user variables that you set up earlier in the Test SSIS package at the start of this External Management and WMI Task Implementation Topic . The results also correctly show that the myExpression variable is an expression-based variable using the EvaluateAsExpression property. The power of the application and package objects don’t stop there. You can also move packages from one store to another, as shown in this next example

Perfect guide for getting started to applied SSIS. Access to freeSSIS Tutorials

Package Monitoring

You saw an example earlier how the Catalog class exposes a property named Executions, which is a collection of ExecutionOperation objects. You can iterate through the ExecutionOperation objects to get a list of the packages that previously ran or a list of currently running packages. The ExecutionOperation class has properties that give you the details of the execution operation such as the package name, the project name, when the package starting running, whether or not the package is completed, when the package completed running, and other useful information about the execution.
The following example (from the MomDemo folder) shows you how to list the packages that are currently running in an SSIS catalog.

Catalog catalog = isServer.Catalogs[“SSISDB”];
StringBuilder messages = new StringBuilder();
foreach(ExecutionOperation exec in catalog.Executions)
if (!exec.Completed)
messages.AppendLine(“Package ” + exec.PackageName ” is running.
The project name is ” + exec.ProjectName);
LogFileTextbox.Text = messages.ToString();

For previous versions of SQL Server or if you’re using the package deployment model, the Application class exposes a method to enumerate all the packages that are currently being executed on an SSIS server. By accessing a running package, you can view some general properties of the package’s execution status, and you can even stop a package’s execution status. The following methods can be used:

  • GetRunningPackages: Returns a RunningPackages object that enumerates all the packages currently running on a server
  • RunningPackages: A collection of RunningPackage objects
  • RunningPackage: An informational object that includes such information as package start time and current running duration

The following code (from the ExtMgt folder in CSharp_GetRunningPkgs or vb_GetRunningPkgs) uses the GetRunningPackage object to enumerate information about each running package, such as the package’s start time and running duration.

private static void GetRunningPackageInformation(string Server)
Application app = new Application();
RunningPackages runPkgs = app.GetRunningPackages(Server);
Console.WriteLine(“Running Packages Count is {0}”, runPkgs.Count);
foreach(RunningPackage pkg in runPkgs)
Console.WriteLine(“Instance ID: {0}”, pkg.InstanceID);
Console.WriteLine(“Package ID: {0}”, pkg.PackageID);
Console.WriteLine(“Package Name: {0}”, pkg.PackageName);
Console.WriteLine(“User Name Running Package: {0}”, pkg.UserName);
Console.WriteLine(“Execution Start Time: {0}”,
Console.WriteLine(“Execution Duration: {0} secs”,
Private Sub GetRunningPackageInformation(ByVal Server As String)
Dim app As New Application()
Dim runPkgs As RunningPackages = app.GetRunningPackages(Server)
Console.WriteLine(“Running Packages Count is {0}”, runPkgs.Count)
For Each RunningPackage In runPkgs
Console.WriteLine(“Instance ID: {0}”, RunningPackage.InstanceID)
Console.WriteLine(“Package ID: {0}”, RunningPackage.PackageID)
Console.WriteLine(“Package Name: {0}”,-
Console.WriteLine(“User Name Running Package: {0}”, _
Console.WriteLine(“Execution Start Time: {0}”, _
Console.WriteLine(“Execution Duration: {0} secs”, _
End Sub

To see this in action, run your SSIS package, and then run this code to see any package that is currently running. This type of code can be useful for monitoring your server to determine whether any packages are running prior to shutting down the server. For example, you may need to get an inventory of the packages on a server. If so, you’ll want to review the next section, which shows you how to do that programmatically.

Project, Folder, and Package Listing

When you want to see a listing of the projects, folders, and packages for a catalog, this is easily achieved by iterating through a few collection classes. The first Collection class that you need to iterate through is the FolderCollection class, which is accessed through the Folders property on the Catalog class. Next, you will iterate through the ProjectsCollection class that is accessed through the Projects property on the Folder class. Finally you’ll iterate through the PackageCollection class, which is accessed through the Packages property on the Project class. This is useful for taking an inventory of all the packages that exist on a server. Here is the code (from the MomDemo folder) that will do this for a catalog on a 2014 SSIS Instance by adding the catalog, catalog folders, projects, and packages to a TreeView.

Server server = new Server(@”localhost”);
IntegrationServices isServer = new IntegrationServices(server);
Catalog catalog = null;
// we’re assuming we don’t know the name of the catalog here
foreach (Catalog c in isServer.Catalogs)
catalog = c;
TreeNode catalogNode = new TreeNode(“Catalog: ” + catalog.Name);
foreach (CatalogFolder f in catalog.Folders)
TreeNode folderNode = new TreeNode(“Folder: ” + f.Name);
foreach (ProjectInfo p in f.Projects)
TreeNode projectNode = new TreeNode(“Project: ” +
.PackageInfo pkg in p.Packages)
TreeNode packageNode = new TreeNode(“Package:
” +

The result is information about the packages stored on the server instance, as shown in below screen shot.

This is only the tip of the iceberg in terms of what you can do with the DTS runtime libraries. To get an idea of what you can do using this library, in the next section you build a simple UI that enables you to use some of the code techniques described so far.

Frequently Asked SSIS Interview Questions & Answers

A Package Management Example

The following example demonstrates how to incorporate package management operations in a web-based application. It shows how to enumerate the catalog, folder, and project structure of a SQL Server SSIS instance; enumerate the packages that are contained in a selected project; and then execute a package from the web page itself. This External Management and WMI Task Implementation Topic demonstrates with a C# version of the project. However, you can download a VB.NET version of the project.

To start, first create a new web project in Visual Studio. Launch Visual Studio and select File ⇒ New ⇒ Web Site. In the New Web Site dialog (as shown in below screen shot), select ASP.NET Web Forms Site and then choose Visual C# or Visual Basic as the language. Leave the rest of the fields as shown.

Click the OK button, and the Web Site project will be initialized. By default, the Default.aspx page is created and displayed automatically. Now you can start building the page that will display the information you want. First, you must add the web controls to the page.

To do this, select the Design view from the bottom-left corner of the Default.aspx tab. This puts the interface into graphics designer mode. From the Toolbox on the left-hand side of the window, drag a TreeView control onto the page. The TreeView control is in the Navigation group of the Toolbox. Now drag a GridView control onto the page. The GridView is located in the Data group of the Toolbox. Finally, drag over a Button control from the Toolbox. The Button control can be found in the Standard group. Click the Button control, and in the Properties tab change the Text property to the word “Refresh.”

Now you need to add some supporting HTML in the source view of the page to configure the columns of the GridView control. To do so, click the Source button on the bottom left of the Default.aspx tab. This switches the view to show you the HTML code that defines this page. Add the following HTML code between the elements. The elements you’re adding configure the GridView to display three data columns and a button column. You could do this through the Design interface, but this is a bit quicker for your purposes:

Select GridView1 in design mode to show the properties. If your properties bar isn’t visible, press F4. Now set the “AutoGenerateColumns” property of GridView1 to false.

The page should now look like below screen shot

Before you leave this screen, you need to create a few event handlers on these controls. To do this, select the TreeView control. Go to the Properties tab in the bottom right of the Visual Studio IDE. On the toolbar of the Properties window, select the lightning bolt symbol that signifies the Events view. The Events view enables you to configure the event handlers you need to handle for this page. With the TreeView selected and the Events view shown in the Properties window, double-click in the SelectedNodeChanged event in the Behavior group. Notice that the Default.aspx.cs code-behind page is automatically loaded, and the event handler code for the SelectedNodeChanged event is automatically created. Switch back to the Default.apsx tab and do the same thing for the TreeView Load event. Now repeat the same process for the GridView RowCommand event and the Button Click events. To view a description of what these events do, you can search for the event name in the Help screen.

The full HTML code of the page (see the CSharp_Website2014 project) should now look something like this:

Now you need to start adding the code behind the page that makes this page work. For this example, you will be creating a few custom classes to support code you will be writing in the code-behind page of the Web Form. First, you need to add two new class files. To do this, select File ⇒ New ⇒ File from the main menu. In the Add New File dialog that appears, select a new Class object and name it PackageGroup.cs. The PackageGroup object will be used to wrap a PackageInfo object and enhance its functionality. Next, add another Class object and call this one PackageGroupCollection.cs. Notice that these two files have been added to the App_Code directory of the solution. In Visual Studio, your code external modules are stored in the App_Code directory. Add the following references to your project:


Next, open the PackageGroup.cs file and add the following code to it (see the CSharp_Website2014 project). You can overwrite the code that was automatically generated with this code:

As you can see, this object stores the information about a package and wraps a PackageInfo object. You could just link the PackageInfo objects to the GridView, but this method codes a wrapper with additional functionality to determine a package’s execution status and execute a package. The ExecutePackage method can be called to execute the package, and the GetPackageStatus method searches the currently running packages on the server and returns an execution status to the calling object.

To store information about multiple packages, you need to roll all the PackageGroup objects you create into a collection object. To do this, you created a strongly typed collection class called PackageGroupCollection to house very concrete PackageGroup objects. Open the PackageGroupCollection file and add the following code (see the CSharp_Website2014 project). Once again, you can overwrite the code that was automatically created when the file was created with this example code:

This class simply inherits from the System.CollectionBase class to implement a basic IList interface. To learn more about strongly typed collections and the CollectionBase class, search the Help files. Next you will add the code-behind page of the Default.aspx page. Select the Default.aspx.cs tab and add the following code (see the CSharp_Website2014 project) to this page:

When the page is processed, several additional methods are called. The TreeView_Load method is called, which in turn calls the LoadTreeView method, which accepts a Catalog collection. The folders and projects of the catalog are iterated, and each folder and project is added to the TreeView. When the page is first loaded, just the TreeView is displayed. By selecting a project in the TreeView, the page is posted back to the server, and the TreeView1_SelectedNodeChanged method is called. This method calls another method in this page called BuildPackageGroupCollection, which accepts a string value that contains the ValuePath of the selected tree node. The string is split by a forward slash, which stores the value of the catalog, folder, and project. Before iterating through the collection of packages, the IntegrationServices, Catalog, CatalogFolder, and ProjectInfo objects are instantiated. Then the Packages property of the ProjectInfo class is iterated to list the packages stored in the selected project. Once the collection is built, the LoadGridView method is called to link the PackageGroupCollection to the GridView. In the LoadGridView method, the collection is bound to the GridView object. This action automatically loads all the objects in the PackageGroupCollection into the GridView.

How does the GridView know which columns to display? Remember back in the beginning of this example when you added the «asp:BoundColumn» elements to the GridView object. Notice that the DataField attributes are set to the properties of the PackageGroup objects in the PackageGroupCollection object. Therefore, in your walkthrough of the code, the page is basically finished processing, and the results would be displayed to the user in the web page. Try it and inspect what you have so far. Go ahead and build and then run the project. below screen shot shows a sample of what you may see when you run the web page. Your results may vary depending on the folders and packages you have configured in your server.

NOTE You’ll need to click the My ProSSIS Project node to see the packages in the grid

Now take a look at how the status field and Execute button work. When the GridView is loaded with PackageGroup objects, the status property of the PackageGroup class is called. Look in the PackageGroup.cs file and you will see that when the status property is called, the code iterates through a collection of ExecutionOperation objects to determine whether a package is currently running or not. The ExecutionOperation stores the name of the folder and package, and those two properties are compared to the internal _folder and _packageInfo.Name values of the current PackageGroup object. If a match is found, the Completed property of the ExecutionOperation object is checked  for a value of False. If False is returned from the Completed property, a string value of “Executing” is returned to the GridView. If the Completed property returns True or nothing is found in the iteration of the ExecutionOperationCollection object, the value of “Sleeping” is returned. The Execute button works in a similar fashion.

When the Execute button is clicked, the GridView1_RowCommand method is called in the page’s code-behind file. This method re-instantiates the PackageGroup object from the page’s viewstate cache. When found, the package is executed by calling the Execute method of the PackageGroup object. Notice that this call is done in a newly created thread. By design, a web page is processed synchronously. This means that if the package were executed in the same thread, the Execute method would not return until the package was finished executing. Therefore, by starting the package in a new thread, the page can return, and the status of the package can be displayed in the GridView. Give it a try. Make sure your package runs long enough for you to refresh the web page and see the status value change.

Explore SSIS Sample Resumes! Download & Edit, Get Noticed by Top Employers!Download Now!

That’s just a basic implementation of some of the functionality exposed by the Microsoft.SqlServer.Dts.Runtime and Microsoft.SqlServer.Management.Integration namespaces to manage your SSIS packages through managed code. You saw how to obtain a collection of PackageInfo objects and how to leverage the functionality of the objects in an application. In addition, you learned how to run a package and determine which packages are currently running. Obviously, this is a simple application and could stand to be greatly improved with error handling and additional functionality. For example, you could add functionality to cancel a package’s execution, load or delete package files to SQL Server through the website, or modify the code to support the viewing of packages in the SSIS file storage hierarchy.

List of Related Microsoft Certification Courses:

 SSRS  Power BI
 SSAS  SQL Server
 SharePoint  BizTalk Server
 Team Foundation Server  BizTalk Server Administrator


Popular Courses in 2018

Get Updates on Tech posts, Interview & Certification questions and training schedules