A Glimpse of the SSIS Catalog Managed Object Model

Today’s post is from Ke Yang – a developer on the SSIS Team based in Shanghai. It provides a quick overview of the new Managed Object Model (MOM) for the SSIS Catalog.

—————————————

The namespace Microsoft.SqlServer.Management.IntegrationServices, contains a rich set of managed APIs that encapsulate most IS Server T-SQL APIs via ADO.NET commands. It’s not a mere T-SQL wrapper; it supports advanced features such as script generation, query via iterators, SQL Powershell, dependency discovery etc. We made this namespace so that developers can build their applications upon it, without having to build a similar infrastructure from T-SQL.

Now, let’s take a glimpse at some code pieces that use this namespace. You will soon find how handy it is!

Create the Catalog

// Create an IntegrationServices instance using an SMO server connection. // Here SMO = Microsoft.SqlServer.Management.Smo; connection is a ServerConnection. SMO.Server server = new SMO.Server(connection);
IntegrationServices isserver = new IntegrationServices(server);
//Create a catalog under isserver, specify the password. //In current release, we allow only one catalog and fix the name to be "SSISDB"; other names would get an error Catalog catalog = new Catalog(isserver, "SSISDB", "password");
catalog.Create();

Deploy a Project

//stream is a byte[] containing the project binary obtained from Project runtime object model.
//You can use CreateProject() to create a project, PackageItems.Add() and Parameters.Add() to add packages and parameters,
//SaveAs() to save the project binary, and finally use System.IO.File.OpenRead() to read it into byte[].
//Here we omit these steps and assume we've already created packages and parameters within the project.
folder.DeployProject("p1", stream);
folder.Alter();
//Add "folder\env1" as a reference.
folder.Projects["p1"].References.Add("env1", "folder1");
//We're able to also add e1 as a "relative" reference, since it's under the same folder with the project.
folder.Projects["p1"].References.Add("env1");
//Set parameter1 to be referencing an environment variable under e1.
folder.Projects["p1"].Parameters["param1"].Set(ParameterInfo.ParameterValueType.Referenced, "var1");
folder.Projects["p1"].Alter();

Execute and Validate

ProjectInfo p = folder.Projects["p1"];
foreach (var pkg in p.Packages)
{
// We can specify whether to use 32 bit runtime for execution on a 64-bit server
// (here we specify "false"), and specify the how to use references (here we
// validate the package against all its references;
// if it has no references, we use parameter default values). Since we don’t specify any
// specific reference, the 3rd argument is left null.
pkg.Validate(false, PackageInfo.ReferenceUsage.UseAllReferences, null);
//Execute the package. The meanings of the arguments are similar to those in PackageInfo.Validate.
pkg.Execute(false, null);
}
//Validate the project. The meanings of the arguments are similar to those in PackageInfo.Validate.
p.Validate(false, ProjectInfo.ReferenceUsage.UseAllReferences, null);

Get Messages

//Print all operation messages, including the execution and validation messages.
//If we want only execution or validation messages, we can replace the
// "Operations" into "ExecutionOperation" or "ValidationOperation" in below code piece.
catalog.Operations.Refresh();
foreach (Operation op in catalog.Operations)
{
op.Refresh();
foreach (OperationMessage msg in op.Messages)
{
Console.WriteLine(msg.Message);
}
}

It is not there. The DLL appears in GAC though, but not as a file anywhere. There are a few things to keep in mind: the target in your VS may not be .Net 4.0, and the reference you add directly to the project file, then it appears possible to use the IntergationServices class and the rest (as Catalog)

I have a need to dynamically set connection strings at runtime using the Managed Object Model. Is it possible to configure ConnectionManagers in this way? Looking at the PackageInfo and ProjectInfo objects the best I can get right now to set individual Parameters from a list of ParameterInfo which seems to store the information.

doesn't seem to work and throws a "Operation 'Alter' on ProjectInfo[@Name='ProjectName'] failed during execution". Any ideas of another way this can be achieved? If I can't achieve this I may have to resort to Package Deployments which would eliminate the benefits that come with SSIS 2012 .

I am trying to use the overloaded Execute method (technet.microsoft.com/…/hh245662.aspx) to overload Parameters when executing an SSIS packages using the MoM. What format would one use to set the PropertyPath when creating an PropertyOverrideParameterSet object?

Post has great potential , but has this worked for anyone OTHER than Matt? Been at this for 2 days and cannot get past step 0, loading the GD SSIS assembly! MStaffers assume we all have the same setup as they do.