Application Object Maintenance Operations | Wireless Java : Developing with Java 2, Micro Edition

The following methods of the Application object allow you to manage a SSIS package in the Windows file system, in the SSIS package store, or within the SQL Server DBMS. In addition, packages can be managed across server instances. Note that methods that contain DtsServer in their names will apply the operation to the SSIS package store, and methods that contain SqlServer in their names will address operations against the SQL Server package store.

Package Maintenance Operations

The Application object exposes the following methods to manage packages in the Windows file system, the SSIS package store, and SQL Server database instance.

LoadPackage — Loads a package from the file system
LoadFromDtsServer — Loads a package from the specified SSIS package store
LoadFromSqlServer — Loads a package to the specified SQL Server instance
LoadFromSqlServer2 — Loads a package to 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

The following C# code snippets exhibit the use of a few of the package maintenance functions outlined above. The following C# code snippet loads a package into an object variable from the file system. Once this object has been instantiated, you can obtain information about the package and control its execution.

 public void LoadPackage() {     Application dtsapp = new Application();     Package pac = dtsapp.LoadPackage(@"C:\Documents and Settings\SSISPackages\                                      Package1.dtsx", void); }

The next C# code snippet obtains a reference to a package object that exists on SQL Server "Server1" instance. Note that the path to the object is not a file system path but rather the path to the root of the SQL Server package store. The package is then transferred to the SQL Server "Server2" instance by calling the SaveToSqlServer method.

 public void TransferPackage() {     Application dtsapp = new Application ();     Package package1 = dtsapp.LoadFromSqlServer(@"\\Package1", "Server1",                                                 "user1", "password", void);     dtsapp.SaveToSqlServer(package1, void, "Server2", "user1", "password"); }

Server Folder Maintenance

The following methods of the Application object allow for the common maintenance operations on the folder structures:

CreateFolderOnDtsServer — Creates a new folder in the "Stored Packages" node of the application objects server
CreateFolderOnSqlServer — Create a new folder in the specified server's "Stored packages" node using the specified user name and password
RemoveFolderFromDtsServer — Removes the specified folder from the application objects server
RemoveFolderFromSqlServer — Removes the specified folder from the specified server using the supplied user name and password
RenameFolderOnDtsServer — Renames the specified folder on the application objects server
RenameFolderOnSqlServer — Renames the specified folder on the specified server using the supplied user name and password
FolderExistsOnDtsServer — Determines if the specified folder currently exists on the application objects server
FolderExistsOnSqlServer — Determines if the specified folder currently exists on the specified server using the supplied user name and password

To exhibit the use of folder maintenance operations, the following examples show how to employ a few of these methods. The first example will create a new folder named "Primary Packages" in the parent folder "SSIS packages" on the VSTSB2 server instance:

 public static void CreateFolder() {     Application dtsapp = new Application();     dtsapp.CreateFolderOnDtsServer("SSIS Packages", "Primary Packages", "VSTSB2"); }

In this next C# code snippet, the FolderExistsOnSqlServer method returns a Boolean value indicating whether the folder called "Primary Folder" exists on the "VSTSB2" SQL Server instance:

 public static void CreateFolder() {     Application dtsapp = new Application();     bool exists = dtsapp.FolderExistsOnSqlServer("Primary Folder", "VSTSB2", null,                                                  null); }

Package Role Maintenance

The Application object exposes methods that allow for SQL Server roles to be referenced and then assigned to SSIS packages. These methods are valid only for packages stored in the SQL Server package store.

GetPackageRoles — This method takes two string parameters that return the assigned reader role and writer role for the package.
SetPackageRoles — This method sets the reader role and writer role for a package.

Packages installed on a SSIS Instance or SQL Server Instance can be assigned SQL Server roles, giving the users assigned to those roles read and/or write access to the package. Read access gives the user the ability to view and run the package. To be able to modify the package, a user must have been assigned write access. To detail this capability, the following C# code snippet assigns the "dbcreater" role to a package and then checks to ensure that the assignment is in effect:

 public static void GetPackageRoles() {     Application dtsapp = new Application();     string readerRole;     string writerRole;     dtsapp.SetPackageRoles("VSTSB2", @"MSDB\package", "dbcreator", "dbcreator");     dtsapp.GetPackageRoles(@"VSTSB2", @"MSDB\package", out readerRole, out                            writerRole);     System.Diagnostics.Debug.WriteLine(readerRole);     System.Diagnostics.Debug.WriteLine(writerRole); }

Package Monitoring

The Application class exposes a method to enumerate all the packages that are currently being executed on a SSIS server. By accessing a running package, you can gain access to handle package events and control a package's execution status. The methods that can be used here are as follows:

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 C# code snippet uses the GetRunningPackage object to enumerate information about each running package such as the package's start time and running duration:

 public static void GetRunningPackageInformation() {     Application dtsapp = new Application();     RunningPackages RunPks = dtsapp.GetRunningPackages("VSTSB2");     RunningPackagesEnumerator RunPksEnum = RunPks.GetEnumerator();     while (RunPksEnum.MoveNext())     {         RunningPackage RunPk = RunPksEnum.Current;         Console.WriteLine("InstanceID: {0}", RunPk.InstanceID);         Console.WriteLine("PackageID: {0}", RunPk.PackageID);         Console.WriteLine("Packagename: {0}", RunPk.PackageName);         Console.WriteLine("UserName: {0}", RunPk.UserName);         Console.WriteLine("Execution Start Time: {0}", RunPk.ExecutionStartTime);         Console.WriteLine("Execution Duration {0}", RunPk.ExecutionDuration);     } }

A Package Management Example

The following example will demonstrate how to incorporate package management operations in a Web based application. This example will demonstrate how to enumerate the folder structure of a SQL Server SSIS package store, enumerate the packages that are contained in a selected folder, and allow you to execute a package from the Web page itself.

To start, first create a new Web project in Visual Studio 2005. Launch VS2005 and select File New Web Site. In the New Web Site dialog (see Figure 16-1), choose Visual C# as the language. Leave the rest of the fields as they are.

image from book
Figure 16-1

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'll 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 DataGrid is located in the Data group of the Toolbox. And finally drag over a Button control from the Toolbox. The Button control can be found in the Standard group. The page should look like Figure 16-2.

image from book
Figure 16-2

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 allows you to configure what event handlers you will 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.

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, select the Source button on the bottom left of the Default.aspx page tab. This switches the view to show you the HTML code that defines this page. Add the following HTM code between the <asp:GridView1> elements. The <asp:BoundField> elements you're adding configure the GridView to display three data columns and a button column. This could be done through the design interface, but this is a bit quicker for your purposes:

 <Columns>    <asp:BoundField DataField="PackageName" HeaderText="Name" />    <asp:BoundField DataField="PackageFolder" HeaderText="Folder" />    <asp:BoundField DataField="Status" HeaderText="Status" />    <asp:ButtonField Text="Execute" ButtonType=Button/> </Columns>

The full HTML code of the page should now look something like this:

 <%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default2" %> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd"> <html xmlns="http://www.w3.org/1999/xhtml" > <head runat="server">     <title>Untitled Page</title> </head> <body>     <form  runat="server">     <div>         <asp:TreeView  runat="server" ShowLines="True"              OnLoad="TreeView1_Load" OnSelectedNodeChanged=              "TreeView1_SelectedNodeChanged">         </asp:TreeView>         <br />         <asp:GridView  runat="server" AutoGenerateColumns=False              OnRowCommand="GridView1_RowCommand" >             <Columns>                 <asp:BoundField DataField="PackageName" HeaderText="Name" />                 <asp:BoundField DataField="PackageFolder" HeaderText="Folder" />                 <asp:BoundField DataField="Status" HeaderText="Status" />                 <asp:ButtonField Text="Execute"  ButtonType=Button/>             </Columns>         </asp:GridView>         &nbsp;<br />         <asp:Button  runat="server" OnClick="Button1_Click"                        Text="Refresh" /></div>     </form> </body> </html>

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 box 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 2005, your code external modules are stored in the App_Code directory. Next, open the PackageGroup.cs file and add the following code to the file. You can overwrite the code that was automatically generated with this code.

 using System; using System.Data; using System.Configuration; using System.Web; using System.Web.Security; using System.Web.UI; using System.Web.UI.WebControls; using System.Web.UI.WebControls.WebParts; using System.Web.UI.HtmlControls; using Microsoft.SqlServer.Dts.Runtime; /// <summary> /// Summary description for PackageGroup /// </summary> /// public class PackageGroup {   Application dtsapp;   public PackageGroup(PackageInfo packageInfo, string server)   {     dtsapp= new Application();     _packageinfo = packageInfo;     _server = server;   }   private PackageInfo _packageinfo;   private string _server;   public string PackageName   {     get { return _packageinfo.Name;}   }   public string PackageFolder   {     get{return _packageinfo.Folder;}   }   public string Status   {     get { return GetPackageStatus(); }   }   public void ExecPackage()   {     Package p = dtsapp.LoadFromSqlServer(string.Concat(_packageinfo.Folder + "\\" +              _packageinfo.Name) , _server, null, null, null);     p.Execute();   }   private string GetPackageStatus()   {     RunningPackages rps= dtsapp.GetRunningPackages(_server);     foreach(RunningPackage rp in rps)     {       if (rp.PackageID == new Guid(_packageinfo.PackageGuid))       {         return "Executing";       }     }     return "Sleeping";   } }

As you can see, this object wraps a PackageInfo object. You could just link the PackageInfo objects to the GridView, but I wanted to create 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.

But to store information on multiple packages, you need to roll all the PackageGroup objects you create into a collection object. To do this, you created a strongly typed PackageGroup collection class called PackageGroupCollection to house PackageGroup objects. Open the PackageGroupCollection.cs file and add the following code to the file. Once again, you can overwrite the code that was automatically created when the file was created with this example code.

 using System; using System.Data; using System.Configuration; using System.Web; using System.Web.Security; using System.Web.UI; using System.Web.UI.WebControls; using System.Web.UI.WebControls.WebParts; using System.Web.UI.HtmlControls; /// <summary> /// Summary description for PackageGroupCollection /// </summary> /// public class PackageGroupCollection : System.Collections.CollectionBase {   public PackageGroupCollection()   {   }   public void Add(PackageGroup aPackageGroup)   {     List.Add(aPackageGroup);   }   public void Remove(int index)   {     if (index > Count - 1 || index < 0)     {       throw new Exception("Index not valid!");     }     else     {       List.RemoveAt(index);     }   }   public PackageGroup Item(int Index)   {     return (PackageGroup)List[Index];   } }

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 to this page.

 using System; using System.Data; using System.Data.SqlClient; using System.Configuration; using System.Web; using System.Web.Security; using System.Web.UI; using System.Web.UI.WebControls; using System.Web.UI.WebControls.WebParts; using System.Web.UI.HtmlControls; using System.Threading; using Microsoft.SqlServer.Dts.Runtime; public partial class _Default : System.Web.UI.Page {   Application dtsapp;   PackageGroupCollection pgc;   protected void Page_Load(object sender, EventArgs e)   {     //Initialize Application object     dtsapp = new Application();   }   protected void TreeView1_Load(object sender, EventArgs e)   {     //Clear TreeView and Load root node     //Load the SqlServer SSIS folder structure into tree view and show all nodes     TreeView1.Nodes.Clear();     TreeView1.Nodes.Add(new TreeNode("MSDB", @"\"));     LoadTreeView(dtsapp.GetPackageInfos(@"\", "localhost", null, null));     TreeView1.ExpandAll();   }   protected void TreeView1_SelectedNodeChanged(object sender, EventArgs e)   {     //Build Collection of PackageGroups     pgc = BuildPackageGroupCollection(dtsapp.GetPackageInfos(            TreeView1.SelectedNode.ValuePath.Replace('/', ‘\\'),            "localhost", null, null));     //Rebind the GridView to load Package Group Collection     LoadGridView(pgc);     //Store the Package Group Collection is Session State     Session.Add("pgc", pgc);   }   protected void GridView1_RowCommand(object sender, GridViewCommandEventArgs e)   {     if (((Button)e.CommandSource).Text == "Execute")     {       pgc = (PackageGroupCollection)Session["pgc"];       PackageGroup pg = pgc.Item(Convert.ToInt32(e.CommandArgument));       Thread oThread = new System.Threading.Thread(new                        System.Threading.ThreadStart(pg.ExecPackage));       oThread.Start();       LoadGridView(pgc);     }   }   protected void LoadTreeView(PackageInfos pis)   {     foreach (PackageInfo p in pis)     {       if (p.Flags == DTSPackageInfoFlags.Folder)       {         TreeNode n = TreeView1.FindNode(p.Folder);         n.ChildNodes.Add(new TreeNode(p.Name));         LoadTreeView(dtsapp.GetPackageInfos(p.Folder + '/' + p.Name, "localhost",                      null, null));       }     }   }   protected void LoadGridView(PackageGroupCollection pgc)   {     GridView1.DataSource = pgc;     GridView1.DataBind();   }   protected PackageGroupCollection BuildPackageGroupCollection(PackageInfos         packageInfos)   {     PackageGroupCollection pgc = new PackageGroupCollection();     foreach (PackageInfo p in packageInfos)     {       if (p.Flags == DTSPackageInfoFlags.Package)       {         PackageGroup pg = new PackageGroup(p, "localhost");         pgc.Add(pg);       }     }     return pgc;   }   protected void Button1_Click(object sender, EventArgs e)   {     LoadGridView((PackageGroupCollection)Session["pgc"]);   } }

The preceding code handles the execution of the page request. First is the Page_Load method that is run every time the asp worker process loads the page to be processed. In this method, an Application object is loaded for use during the processing of the page.

When the page is processed, there are several additional methods that are called. The TreeView_Load method is called. This method in turn calls the LoadTreeView method that accepts a PackageInfos collection. This collection of PackageInfo objects is processed one by one, and the information is loaded into the TreeView according to the hierarchy of the SQL Server SSIS package folders. When the page is first loaded, just the TreeView is displayed. By selecting a folder 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 PackageInfos collection. The PackageInfos collection is processed to look for valid package objects only. To determine this, the PackageInfo class exposes a Flag property that identifies the PackageInfo object as a Folder or a Package object. 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.

So 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. So in your walk-through of the code, the page is basically finished processing and the results would be displayed to the user in the Web page. So try it and inspect what you have so far. Go ahead and build and then run the project. Figure 16-3 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.

image from book
Figure 16-3

So 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, a collection of RunningPackages is created. By iterating through all the RunningPackage objects, if the GUID of the package in question matches the GUID of a running package, a result of Executing is returned to the GridView. Otherwise, the Sleeping status result 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 being done in a newly created thread. By design, a Web page is processed synchronously. This means that if the package was executed in the same thread, the Execute method would not return until the package was finished executing. So by starting the package in a new thread, the page can return, and the status of the package can be displayed in the GridView. So give it a try. Make sure your package runs long enough for you to refresh the Web page and see the status value change.

That's just a basic implementation of some of the functionality exposed by the Microsoft.SqlServer.Dts.Runtime namespace 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 Web site, or modify the code to support the viewing of packages in the SSIS file storage hierarchy.