A Glimpse of the SSIS Catalog Managed Object Model

A Glimpse of the SSIS Catalog Managed Object Model

Rate This
  • Comments 9

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);
    }
}
Leave a Comment
  • Please add 2 and 5 and type the answer here:
  • Post
  • I don't understand what message you are trying to convey.

  • I don't understand what message you are trying to convey.

  • I can't find Microsoft.SqlServer.Management.IntegrationServices.dll.  I have sql2012 installed and this file is not in the GAC or anywhere to be found.

  • 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)

    There was a discussion here: http://bit.ly/JTe3Ap that may be beneficial.

    Thing is I get warnings switching to 3.5 as my target regarding the Microsoft.SQLServer.ManagedDTS reference in my VS 2010.

  • Note that the Microsoft.SqlServer.Management.IntegrationServices.dll file can be found in the .NET 2/3.5 GAC (C:\windows\assembly) in SQL 2012 RTM.

  • Hi Matt,

    How can we delete any folder inside catalog using powershell?

  • 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.

    project.Parameters["CM<myconnectionmanagername>.UserName"].Set(ParameterInfo.ParameterValueType.Literal, "user");              

    project.Parameters["CM.<myconnectionmanagername>.Password"].Set(ParameterInfo.ParameterValueType.Literal, "password");

    project.Alter();

    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 .

  • There was an inner exception which was being thrown by the alter operation.  Add the following to app.config fixed my issue and allowed it to work:

    startup useLegacyV2RuntimeActivationPolicy="true">

    <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.5"/>

    </startup>

  • 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?

Page 1 of 1 (9 items)