External Management with Managed Code

The SSIS development team has done a terrific job in providing a robust architecture to manage SSIS through managed code. Managed code in this case refers to the use of the .Net Framework Common Language Runtime that hosts code written in C# or VB.NET.

Through a rich object model, you can customize your applications to control almost every aspect of a SSIS server instance. This section will attempt to provide a brief overview of the SSIS programming model as it applies to externally managing a SSIS server instance and the SSIS packages it contains.

To start using these objects exposed by the Microsoft.SqlServer.Dts.Runtime namespace, you must first have created a project in Visual Studio 2005. If you are unfamiliar with how to do this, the example presented later in this chapter will walk you through the creation of a simple Web page that utilizes these operations. But for now, I will generally describe the functionality available through code snippets. If you are unfamiliar with C#, I would suggest you investigate the language to fully understand these code snippets and how the example works.

The classes in this namespace are encapsulated by the Microsoft.SQLServer.ManagedDTS class library. Add this reference to a new console application and investigate the classes exposed for your use. Pay particular attention to the members exposed by the Application and Package objects. These are the core objects you will be working with.

The Application object is the core class that exposes methods used to connect to and interface with a SSIS service instance. The following typical management operations can be performed through this class:

• Load, save, execute, and delete SSIS packages on the Windows files system, SQL Server, or Integration Services repository

• Add, remove, and rename folders in SQL Server or Integration Services repository folders

• Control package permissions stored within a SQL Server

• Obtain state information and status regarding the execution of packages in SQL Server or the SSIS package repository

The Package object represents an instance of a single SSIS package. Although this object exposes many methods that allow you to control every aspect of a package, this chapter will only deal with functionality as it could apply to maintenance-type operations. So, keeping that in mind, the Package object exposes the following functionality that you can use in maintenance-type scenarios like these:

• Configure Log Providers

• Manage Package Configurations

• Manage Connection Managers in SQL Server and Integration Services