May 24, 2019
Hot Topics:

Using the SQL Server 2005 Integration Services Class Library

  • December 2, 2005
  • By Jeffrey Juday
  • Send Email »
  • More Articles »

Sample Program Classes

As discussed previously, ExploreIS reads some of the contents of a SSIS package. ExploreIS contains three key classes with the following roles:

  • DTSPackageWrapper abstracts the navigation of the package class through a ReadNextContent function.
  • DTSPackageContent reads the contents of Tasks, TaskHosts, ConnectionManagers, and Data Flow Components. Its overload constructor accepts Executable objects and ConnectionManager objects.
  • DTSPackageContentItem stores what the DTSPackageContent class reads.

When ExploreIS executes, DTSPackageWrapper uses the SSIS Package class to open the Package and iterate over the Executable and Connections collections.

The heart of ExploreIS lies in the DTSPackageContent functions LoadExecutableContent and LoadConnectionContent. LoadExecutableContent and LoadConnectionContent work in similar ways, but LoadExecutableContent is much more complicated. The remainder of the article focuses on the LoadExecutableContent function.

Finding the Task

The SSIS Package object's Executables collection can contain TaskHost as well as other classes that represent other items in a SSIS package. When the DTSPackageContent object is initialized using the Executable constructor, LoadExecutableContent executes in response to the LoadContentItems function call.

The following code snippet is from the LoadExecutableContents function:

private void LoadExecutableContents()
   TaskHost th;
   MainPipe mp;
   WriteContentItemToCollection("*-" + _exec.ToString());
   if (_exec is TaskHost)
      th = _exec as TaskHost;
      WriteContentItemToCollection("**" + th.Name);
      if (th.InnerObject is MainPipe)
         mp = th.InnerObject as MainPipe;

As discussed previously, TaskHost contains the Task object. ExploreIS outputs the name of the SSIS Task. Data Flow Tasks get special treatment. The MainPipe class encapsulates the functionality of the Data Flow Task in the SSIS object library.

Now, you can turn to the useful collections in the MainPipe class.

Iterating Through the Data Flow Task PipelineComponents

As stated previously, the Data Flow Task is implemented by using unmanaged code and accessed through the MainPipe class. The following code snippet illustrates how information about the PipelineCompents setup in the Data Flow Task can be accessed by using collections in the MainPipe class:

mp = th.InnerObject as MainPipe;
WriteContentItemToCollection("- MainPipe=" + mp.ToString());
foreach (IDTSPath90 pat in mp.PathCollection)
   WriteContentItemToCollection("-- Path90=" + pat.Name);
//The foreach below accesses each component in the pipeline
foreach (IDTSComponentMetaData90 md in

Putting the Object Model to Use

Certainly, if you want to extend SSIS, you must understand the object model. However, the object model does have other uses. For example, one of the motivations for developing ExploreIS was to investigate how to use the object model to extract information for the Meta-data Repository in a third-party tool.

Also, many of the SSIS samples included in SQL Server 2005 show you how to create SSIS packages programmatically. You can use the SSIS object model to make SSIS part of a separate custom application. For example, you could build a tool that executes SSIS without a DTSX file.

Many other classes may be of further use to you. SQL Server 2005 Books Online includes more suggestions and it documents the Object Model in further detail.

No Class Knowledge Required

You don't have to understand all the classes in an object model to gauge how you should interact with it. ExploreIS illustrates how you can read a SQL Server Integration Services Package using a handful of key classes.

Download the Code

To download the accompanying source code for the examples, click here.

About the Author

Jeffrey Juday is a software developer with Crowe Chizek in South Bend, Indiana. He has been developing software with Microsoft tools for more than 12 years in a variety of industries. Jeff currently builds solutions using BizTalk 2004, ASP.NET, SharePoint, and SQL Server 2000. You can reach Jeff at jjuday@crowechizek.com.

Page 2 of 2

Comment and Contribute


(Maximum characters: 1200). You have characters left.



Enterprise Development Update

Don't miss an article. Subscribe to our newsletter below.

Thanks for your registration, follow us on our social networks to keep up-to-date