Running SSIS Packages Programmatically, Part 4: Project Deployment Model changes

Resources:

New for 2012, the Project Deployment model is very different from the existing, or Package, deployment model. Read up here if you want more info on the differences.
From the .NET perspective, there’s an entirely new API to use. The bad news is that we have to write more code, but the good news is that the API is much more fully formed. Let’s take a look:

//using Microsoft.SqlServer.Management.IntegrationServices;
SqlConnection sqlconn = new SqlConnection(connectionstring);
IntegrationServices isService = new IntegrationServices(sqlconn);

First, we need to create an instance of the IntegrationServices class, with a connection string to the server; note that the database isn’t needed to be set to SSISDB as the API will figure it out.

Next, we need to specify the Catalog, Catalog Folder, Project, and Package. If you’ve tried to execute a Project-Deployed package via T-SQL, this should look familiar:

Catalog ssisdb_cat = isService.Catalogs["MyCatalog"];
CatalogFolder ssisdb_folder = ssisdb_cat.Folders["MyCatalogFolder"];
ProjectInfo ssisdb_Project = ssisdb_folder.Projects["MyProject"];
PackageInfo pkg = ssisdb_Project.Packages["MyPackage"];

If you want, you can first check if the object exists as each collection has a Contains() method:

Catalog ssisdb_cat = null;
CatalogFolder ssisdb_folder = null;
ProjectInfo ssisdb_Project = null;
PackageInfo pkg = null;

if (isService.Catalogs.Contains("MyCatalog"))
{
    ssisdb_cat = isService.Catalogs["MyCatalog"];
    if (ssisdb_cat.Folders.Contains("MyCatalogFolder"))
    {
        ssisdb_folder = ssisdb_cat.Folders["MyCatalogFolder"];
        if (ssisdb_folder.Projects.Contains("MyProject"))
        {
            ssisdb_Project = ssisdb_folder.Projects["MyProject"];
            if (ssisdb_Project.Packages.Contains("MyPackage"))
            {
                pkg = ssisdb_Project.Packages["MyPackage"];
            }
        }
    }
}

if (pkg == null)
{
    throw new Exception("Package not found!");
}

Next step is to set the project parameters. I recommend setting the logging level to Basic:

Collection<PackageInfo.ExecutionValueParameterSet> packageparams = new Collection<PackageInfo.ExecutionValueParameterSet>();

packageparams.Add(
    new PackageInfo.ExecutionValueParameterSet
    {
        ObjectType = 50,
        ParameterName = "LOGGING_LEVEL",
        ParameterValue = 1
    }
);

A big change is that packages now default to running asynchronously. If this isn’t the desired behavior (probably not), we have to configure synchronous execution:

packageparams.Add(
    new PackageInfo.ExecutionValueParameterSet
    {
        ObjectType = 50,
        ParameterName = "SYNCHRONIZED",
        ParameterValue = 1
    }
);

…unless your package takes more than 30 seconds to run. [note: not yet tested in SQL 2014]

(BTW, these configurations can be done executing via Stored Procedure as well).

If there are parameters to set:

packageparams.Add(
    new PackageInfo.ExecutionValueParameterSet
    {
        ObjectType = 30, //package parameter:
        ParameterName = "InputFile",
        ParameterValue = inputFile
    }
);

packageparams.Add(
    new PackageInfo.ExecutionValueParameterSet
    {
        ObjectType = 20,//project parameter:
        ParameterName = "DestinationDB",
        ParameterValue = destinationDB
    }
);

Finally, execution of the package is simple:

long executionIdentifier = pkg.Execute(false, null, packageparams);

Note the return value; the Executions and Operations collections of the catalog hold info on the outcome of the package execution(again, just like with execution via Stored Procedure). This code looks for Operation Messages with
message level
120 (Errors):

ExecutionOperation execution = poisecat.Executions[executionIdentifier];
List<OperationMessage> errormsgs = execution.Messages.Where(x => x.MessageType == 120).ToList();

And that’s it!
However…switching to Project Deployment model also means that a service is executing our package, one which carries through the client’s credentials. If this is a new execution scenario, and outside machines are accessed, the dreaded-but-obscure Kerberos Double-Hop issue will come into play. Gotcha!

Leave a Reply

Your email address will not be published. Required fields are marked *