External Management of SSIS

The SSIS development team has exposed a robust architecture to manage SSIS through managed code. The managed code in this case refers to the use of the .NET Framework Common Language Runtime (CLR), which hosts code written in C# or VB.NET.

Through a rich object model, you can customize your applications to control almost every aspect of managing an SSIS project and package. This section provides a brief overview of the SSIS programming model as it applies to externally managing SSIS projects and packages.

Setting Up a Test SSIS Package for Demonstration Purposes

For this External Management and WMI Task Implementation Topic, we’ll be adding a new SSIS package to the project created in Programming and Extending SSIS Topic named ProSSIS2014_SSIS. Note that all the code in this External Management and WMI Task Implementation Topic can be downloaded from WWW.WROX.COM/GO/PROSSIS2014. The package you will set up for this External Management and WMI Task Implementation Topic is designed specifically to highlight some of the capabilities of using the managed code libraries.

To start, open the ProSSIS2014_SSIS and add a new package. Name the package package.dtsx. This package will contain a simple Script Task that runs for 10 seconds.

Add two variables to the package as follows:

Now drop a Script Task on the Control Flow surface. For the Script Task, set the script language to C# and set the code like this (see the testSSISPackage project):

Test the package to ensure that everything is working correctly. Your results should resemble below screenshot.

Now that you have a working test package, you can use it to examine the management capabilities of the DTS runtime managed code library.

The Managed Object Model Code Library

To start with the external management examples in this External Management and WMI Task Implementation Topic, you need to have SQL Server installed (with SSIS). You also need the Visual Studio project templates for developing console, windows, and web applications. The code is simple enough to follow along, and as always you can download it from WWW.WROX.COM/GO/PROSSIS2014. If you have installed SSIS, you will find a DLL registered in the global assembly cache (GAC) named Microsoft.SQLServer.Management.IntegrationServices.dll (version 11 or higher). In this DLL is a namespace called Microsoft.SqlServer.Management.IntegrationServices nicknamed “MOM,” which stands for Managed Object Model. To access the classes in this namespace, you must first create a project in Visual Studio and then add a reference to the namespace for Microsoft.SQLServer.Management.IntegrationService.dll. To avoid typing the full namespace reference, you’ll want to add either an Imports or a Using statement to include the namespace in your code classes like this:

The Managed Object Model relies on the use of SMO (Server Management Objects), which means you will need to reference the Microsoft.SqlServer.Smo.dll, Microsoft.SqlServer.ConnectionInfo.dll, and Microsoft.SqlServer.Management.Sdk.Sfc.dll. All of these assemblies can be found in the GAC. If you try to compile a project without these references, you will receive an error stating the references will need to be added. For the Managed Object Model examples you will also need to add the Imports or Using statement for SMO like this:

The Managed Object Model contains the classes used to manage SSIS server instances for SQL Server 2014. This includes managing SSIS catalogs, projects, packages, parameters, environments, and operations.

The IntegrationServices class is the gateway to a SQL Server 2014 SSIS instance. Its primary feature is to be used as the programmatic reference to an SSIS instance.

The Catalog class is used to create and manage the single catalog allowed in SQL Server 2014. The following are typical operations of the Catalog class:

• Create, manage, and remove an SSIS catalog.
• Provide the Folders property, which contains a collection of CollectionFolder objects.
• Provide the Operations property, which contains a collection of Operation objects. Operations include log messages, validation results, and so on.
• Provide the Executions property, which contains a collection of ExecutionOperation objects. Executions are log events of when a package executed or will show that a package is executing.

The CatalogFolder class represents a folder that belongs to a catalog. Typical operations performed by the CatalogFolder are:

• Create, manage, and remove folders
• Deploy and remove projects
• Manage permissions to folders
• Create, manage, and remove environments
• Provide the Projects property, which is a collection of projects for the folder

Now that you’ve been introduced to the core classes you’ll be using, it’s time to go ahead and write some code.

Catalog Management

Our first example (from the MomDemo project) is simply connecting to the local instance of SQL Server 2014 and creating a new catalog. Even though the Managed Object Model provides a CatalogCollection class, there can be only one catalog in an SSIS instance. A side effect of having only one catalog is that if you try to execute the code example that follows more than once against an SSIS instance, you will receive an error stating that SSIS supports only one catalog.

// create an instance of the “MOM” class and initialize it with the
SMO Server
IntegrationServices isServer = new IntegrationServices(server);
// create an instance of the Catalog class. The parameters are
(server, name of
catalog, password for catalog)
Catalog catalog = new Catalog(isServer, “SSISDB”, “password”);
// now call create
catalog.Create();

As mentioned previously, the IntegrationServices class represents an instance of SSIS in SQL Server 2014. One of the benefits of the IntegrationServices class is it has more than one constructor that allows you to pass in an SMO Server object, a SqlConnection object, or a SqlStoreConnection object from the Microsoft.SqlServer.Management.Sdk.Sfc class. This flexibility allows the management of the SSIS instance from a variety of different project types.

After the example is executed, you should see the new SSISDB catalog in SSMS under the Integration Services Catalogs folder (see below screenshot).

To remove a catalog from an SSIS instance, you would call the Drop method on a Catalog class. However, because there is only one instance of a catalog, the Drop method shouldn’t be used unless you want to recreate your whole catalog.

Folder Management

In this next example (from the MomDemo project) you are going to create a folder in the newly created catalog by using the CatalogFolder class. The CatalogFolder class allows you to create and manage folders that belong to an SSIS catalog. The catalog created in the last example is being retrieved by using the CatalogCollection class.

// create an instance of the “MOM” class and initialize it with the
SMO Server
IntegrationServices isServer = new IntegrationServices(server);
// retrieve the catalog we created in the last example
Catalog catalog = isServer.Catalogs[“SSISDB”];
// create an instance of the Folder class passing in the catalog,
name of the folder and description of the folder
CatalogFolder folder = new CatalogFolder(catalog, “ProSSIS”,
“Description”);
// now call create
folder.Create();

You saw in this example that you’re able to access the CatalogCollection class by using the Catalogs property that is part of the IntegrationServices class. To retrieve an instance of the Catalog class, you have to either know the name of the catalog or iterate through the collection in the Catalogs property. If you don’t know the name, you will iterate through the collection and grab the first (and only) catalog in the Catalogs property like this:
Catalog catalog = null;
foreach(Catalog c in isServer.Catalogs)
catalog = c;

After running the preceding example, you should have a ProSSIS folder underneath the SSISDB catalog in SSMS (see below screenshot).

If you’re copying the examples from the Tutorial and not using the examples downloaded from WWW.WROX.COM/GO/PROSSIS2014, you need to be aware of an issue when calling Create on the CatalogFolder class that you may run into. You may receive an error stating “Mixed mode assembly is built against version ‘v.2.0.50727’ of the runtime and cannot be loaded in the 4.0 runtime without additional configuration information.” If you receive this error, then you will need to add the following section to the App.Config of your project:

Environments

Environments are containers that store parameters to be used in your packages deployed to an SSIS catalog. They are covered more extensively in Administering SSIS Topic Imagine you have a project that runs on a schedule. On weekdays, the packages in the project need to run with a certain set of parameter values, but on weekends, they need a different set of values. This is a project with several packages, and each package has several parameters. All the packages in the project need to use the same parameter values when they run. Environments make it easy to run these packages with different sets of parameter values.

 

Frequently Asked SSIS Interview Questions & Answers

 

The next example (from the MomDemo project) will show you how to retrieve a catalog folder by name, create an environment, and add variables to the environment by using the EnvironmentInfo and EnvironmentVariables classes.

// grab the “ProSSIS” folder. You will need to
CatalogFolder folder = catalog.Folders[“ProSSIS”];
// create a new Environment
EnvironmentInfo env = new EnvironmentInfo(folder, “Environment1”, “Description”);
env.Create();
// create a non sensitive, integer variable
// the add function parameters are (name of variable, type code, value, is sensitive, description)
e.Variables.Add(“IntVar1”, TypeCode.Int32, 1, false, “Description of IntVar1”);
// create a sensitive (secure), string variable
e.Variables.Add(“SecureVar1”, TypeCode.String, “securevalue”, true, “Description of SecureVar1”);
// now call alter to save the environment
env.Alter();
After the example is run, a new SSIS environment will be created under the ProSSIS folder with two variables, as seen in below screenshot.

Environments can be used when executing packages on the server to inject parameter values into a package. This is especially useful when your package needs to be executed against different environments like development and production environments.

To remove a folder you’d simply call the Drop method and the folder. If the folder contains any projects or environments, those projects and environments have to be removed before dropping the folder. The next example (from the MomDemo project) shows how to iterate through any folder contents and remove them before dropping the folder from the catalog.

This section covered the basics of the Managed Object Model that can be used to manipulate SSIS server instances. At this point you need to be introduced to the DTS Runtime library that will be used to create projects that can be deployed using the Managed Object Model.

The DTS Runtime Managed Code Library

Now that you’ve been introduced to the server management libraries, the next step is to learn about the classes in the Microsoft.SqlServer.Dts.Runtime namespace. For this section, you need to have the SQL Server SDK or SSIS installed. If you have installed the SQL Server SDK or SSIS, you will find a DLL registered in the global assembly cache named Microsoft.SQLServer.ManagedDTS.dll (version 11 or higher). In this DLL is a namespace called Microsoft.SqlServer.Dts.Runtime. To access the classes in this namespace, you must first create a project in Visual Studio and then add a reference to the namespace for Microsoft.SQLServer.ManagedDTS.dll. To avoid having to type the full namespace reference, you’ll want to add either an Imports or a Using statement to include the namespace in your code classes like this:

SSIS Deployment Projects

Prior to SQL Server 2012, an SSIS project was only a basic container in BIDS to store all of your single packages and configurations in a logical group on your development system. The concept of a development environment project did not go away with SQL Server 2012, but the deployment project model alleviates some of the headaches that went along with the deployment of SSIS packages. Administering SSIS Topic covers the deployment model, but this External Management and WMI Task Implementation Topic cover how to create the new project files programmatically.

The deployment package is created automatically when you build an Integration Services project in SQL Server Data Tools (SSDT). The type of file that is created is known as an .ispac file. An .ispac file is basically a zip file with an .ispac extension that stores all of the packages, parameters, project configurations, and so on for easy portability. Because the file is stored in zip format, the only way to create an .ispac file is by either using the SQL Server Data Tools to build the file or creating the file using the Project class.

The Project class was created to allow you to manipulate SSIS deployment projects. The following are typical operations that can be performed with the Project class.

• Create, open, and save SSIS deployment project files
• Add and remove packages to SSIS project files
• Set project level properties like ProtectionLevel and Password
• Add parameters to and remove parameters from projects

The following is a simple example (from the MomDemo project) of how to create an SSIS deployment project, add an SSIS package, and then save it as an .ispac file. I copied the Package.dtsx file from the testSSISProject to the MomDemo project for the following example.

The CreateProject method can be used like it is in the preceding example by passing in a path to a file or by passing in a System.IO.Stream object. A package is added to the project by calling the PackageItems.Add method.

The first parameter accepts a Microsoft.SqlServer.Dts.Runtime.Package object. Because “package.dtsx” already existed on the local machine, instantiating an empty Package class (new Package()) is done to simply create a placeholder object. If you’re creating packages programmatically using the Microsoft.SqlServer.Dts.Runtime.Package class, those packages can be passed into the first parameter instead of the placeholder.

The second parameter is the name of the package being added to the project. In the example, the path of an existing SSIS package was passed into the Add method (package.dtsx).

Parameter Objects

Using Variables, Parameters, and Expressions Topic earlier in the Tutorial introduces parameters. Parameters were added to the SQL Server 2012 version of SSIS to be used in expressions throughout your project and packages. Because project-level parameters are supported, this makes it very easy for a single value to be used across your whole project in several packages.

This is an important feature that was added to allow the reuse of packages across several environments with just a simple change to the parameter. For instance, you could pass in the connection string for an OLE DB connection using a parameter and reuse the same package to execute against your development and production servers.

This next example (from the MomDemo project) takes part of the last example and expands on it by adding a few package parameters and setting the parameter values, as well as saving the project after all the package settings have been added.

In this example, you saw how to add parameters to both the SSIS project and the package that was added to the project. When you are creating parameters, both the name of the parameter and the data type needs to be specified. Most data types are supported, but the following data types cannot be used for parameters: Object, DBNull, Char, and UInt16. Ideally, the parameters should have unique names, but the same parameter name can be used in both a project and a package level parameter, meaning you could have a project parameter named “Connection” and a package level parameter named “Connection.”

The line of code in the preceding example above that calls SetExpression shows you how to access the $Project parameter object that is used to access project-level parameters when using SSIS expressions. Both the project and the package level parameters can be accessed through namespace objects, which are named $Project and $Package, respectively.

Any of the parameters, whether they are stored on the project and parameter level, can be accessed using the $Namespace::ParameterName format (for example, $Package::PackageParameter1 or $Project::ProjectParameter2).

Server Deployment

As mentioned earlier in the Adding a User Interface to Your Component Topic, deployment in SQL Server 2012 changed from a single package deployment to a project deployment model. You can still use the previous deployment model by right-clicking on the SSIS project in SQL Server Data Tools and clicking the “Convert to Package Deployment Model” (see below screenshot), which will revert the deployment model to the pre- 2012 model of storing the packages in the MSDB database or Integration Services store. Keep in mind that if you’re using project or package level parameters in your packages or any task like the Execute Package Task that uses project references, you cannot use the package deployment model.

The next example (from the MomDemo project) opens the project created in the previous example (myproject.ispac) and deploys it to the ProSSIS folder in the SSIS catalog on the local SQL Server 2014 server.

In the preceding example, DeployProject was called using the CatalogFolder class. When you are deploying a project to a catalog, a folder must be used to store the project. In this case, the “ProSSIS” folder was used. The Alter method was called to save the changes made to the folder, which in this example was simply adding a new project.

Executing SSIS Packages Deployed to the SSIS Catalog

Now that a project has been deployed, you can execute a package on the server and grab the execution messages (see the MomDemo project). This is a powerful way of executing a package on the server and easily grabbing any messages that were outputted during execution. We are assuming a reference to the “ProSSIS” catalog folder was already obtained.

When calling the Execute method from the PackageInfo class, you’re able to object the Id of the operation by assigning it to the operationId variable. This is later used in the method when iterating through the Operations property of the Catalog class to retrieve only the OperationMessage references for the package that was just executed.

At the end of the example, we used the Message property of the OperationMessage class to build the messages string builder.

Environment References

We mentioned earlier in the Adding a User Interface to Your Component Topic that environments can be used when executing a package stored in an SSIS catalog. The second parameter of PackageInfo.Execute method accepts an EnvironmentReference object, not an EnvironmentInfo object that was used to create the environment. To use an environment you must add an environment refers to the project either in SSMS (see Administering SSIS Topic) or programmatically using the ProjectInfo.References.Add method. An environment reference is basically a pointer to an existing environment.

This next example (from the MomDemo project) expands on an earlier example where an environment was added to a project by adding an environment refers to a project.

The last few lines of code in this example show you how to add a reference to an environment by utilizing the EnvironmentReference.Add method. Now that a reference has been added, you can use it when executing a package on the server. This next example (from the MomDemo project) shows you how to retrieve an EnvironmentReference object and pass it into the Package.Execute method.

List of Related Microsoft Certification Courses:

SSRS	Power BI
SSAS	SQL Server
SCCM	SQL Server DBA
SharePoint	BizTalk Server
Team Foundation Server	BizTalk Server Administrator