Resources:

• http://msdn.microsoft.com/en-us/library/ms136090.aspx

A brief history of the API: When SQL Server 2005 came out and introduced SSIS (replacing DTS), the developer API was presented, too. Any programmers who were working with this in depth would have seen classes like IDTSComponentMetaData90, IDTSVirtualInputColumnCollection90, etc. What’s the …90 suffix? SQL Server 2005 is version 9.0. Uh, oh. So that means… Yes, in SQL Server 2008, all those class names changed (IDTSComponentMetaData100, IDTSVirtualInputColumnCollection100). Thankfully, they’ve stayed the same ever since. All that is to say, the code I’m writing for SQL Server 2012 will for the most part recompile without issue for SQL Serve 2008 (and R2) by just repointing to the proper assemblies. On to coding:

//using Microsoft.SqlServer.Dts.Runtime;
Application app = new Application()
Package pkg;

The Application class is the main SSIS program. If you’ve ever worked with the Interop classes for Office, you’ve seen something similar. This class will be used to load the Package class, and eventually run it, but there’s a few different ways we can do this. But we need one more class:

class MyEventListener : DefaultEvents
{
    public DtsObject ErrorSource;
    public string ErrorSubcomponent;
    public string ErrorDescription;
    public int ErrorCode;

    public override bool OnError(
        DtsObject source,
        int errorCode,
        string subComponent,
        string description,
        string helpFile,
        int helpContext,
        string idofInterfaceWithError)
    {
        ErrorSource = source;
        ErrorSubcomponent = subComponent;
        ErrorDescription = description;
        ErrorCode = errorCode;

        //Console.WriteLine("Error in {0}/{1} : {2}", source, subComponent, description);
        return false;
    }
}

(This will be discussed in a sec)

1. Load from the File System:

string pkgLocation; //package directory, file name
MyEventListener eventListener = new MyEventListener();
pkg = app.LoadPackage(pkgLocation, eventListener);

The LoadPackage method takes two parameters:

string fileName,
IDTSEvents events //can be null

The IDTSEvents interface specifies methods that are called when events during package execution fire. But this is an interface, so we’ll need an actual class. Luckily, Microsoft provides a base one to inherit from: DefaultEvents.

2. Load from the Integration Services Server, i.e. the SSISDB database. This is new for 2012.

string pkgLocation; //package directory, file name
MyEventListener eventListener = new MyEventListener();
pkg = app.LoadFromDtsServer(pkgPath, SSISServerName, eventListener);

The LoadFromDtsServer method takes three parameters:

string sPackagePath, //package path
string sServerName, //SSIS Server Name
IDTSEvents events //can be null

Otherwise the methods are the same.

2. Load from SQL Server (the old SSIS Package Store: MSDB/Server Directory):

string pkgLocation; //package directory, file name
MyEventListener eventListener = new MyEventListener();
pkg = app.LoadFromSqlServer(pkgPath, SSISServerName, eventListener);

The LoadFromSqlServer method takes four parameters:

string packagePath,
string serverName,
string serverUserName,
string serverPassword,
IDTSEvents events //can be null

Ultimately, all three get you to the same place: a package loaded into memory. What to do with it in the next post…