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();

Create a Folder

//Create a folder under catalog, with folder description 
CatalogFolder f = new CatalogFolder(catalog, "folder1", "Description of folder1.");
f.Create();
//Create an environment under folder1 
EnvironmentInfo e = new EnvironmentInfo(f, "env1", "Description of env1.");
e.Create();
//Add variables into e1. We declare v2 as sensitive. 
e.Variables.Add("var1", TypeCode.Int32, 1, false, "Description of var1.");
e.Variables.Add("var2", TypeCode.String, "sensitive value", true, "");
e.Alter();

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);
    }
}