Using a Reference

Open the Visual Studio solution ExecuteCatalogPackageTask. In Solution Explorer, open the class ExecuteCatalogPackageTask.cs. In the top section of the class, add the line using Microsoft.SqlServer.Dts.Runtime;

Your class now appears as shown in Figure 7-1:

This line of code allows you to use objects, methods, and properties contained in the assembly Microsoft.SqlServer.ManagedDTS – a reference added to this assembly in Chapter 3.

Decorating the Class

If you are reading this near the holidays, I know what you’re thinking: “Andy, the holidays are almost over. Why are we decorating now?” It’s not that kind of decorating.

We decorate to inform Visual Studio this class is different. In this case, the class is different because it is part of an SSIS task. Our decoration code goes just prior to the class definition and is composed of the code in Listing 7-1. We add this code to tell Visual Studio more about what we’re building, as displayed in Figure 7-2:

[DtsTask(

        TaskType = "DTS<version>"

      , DisplayName = "Execute Catalog Package Task"

      , Description = "A task to execute packages stored in the SSIS Catalog."

        )]

Listing 7-1

Decorating the class

The TaskType attribute is optional. SSIS 2019 is version 150. One could argue for permitting SSIS 2012, version 110, for the TaskType attribute in the DtsTask decoration since the SSIS Catalog was introduced with SSIS 2012. Since this project is built using Visual Studio 2019 using the default .Net Framework – 4.7.2 at the time of this writing – the author opted to code the task for SSIS 2019 and later.

Inheriting from Microsoft.SqlServer.Dts.Runtime.Task

Once the reference and decoration are in place, the next step is to configure inheritance from the Microsoft.SqlServer.Dts.Runtime.Task object. Inheriting Microsoft.SqlServer.Dts.Runtime.Task provides a framework for a lot of the work to come, including methods the code will override.

Your class should now appear as shown in Figure 7-3:

The actions taken so far combine to create a relationship between our code and the base code included in the ManagedDTS assembly we referenced and using statement earlier. Now it’s time to build on that relationship by adding the things we want our task to do.

What do we want our task to do? We want to start an SSIS Package that’s been deployed to an SSIS Catalog. To accomplish this, we need to provide the name of a SQL Server Instance that hosts an SSIS Catalog, the name of the SSIS Catalog Folder that contains the SSIS Project, the name of the SSIS Project that contains the SSIS Package, and the name of the SSIS Package.

Adding a Property

Properties provide a mechanism to expose values of internal variables to objects outside the class. In a class, a property may be coded using an internal private variable and publicly accessible property. Properties may also be coded using a get/set at declaration.

The class should now appear as shown in Figure 7-4:

Task properties will contain string values, and we will use the string values internally in the task. For example, the property named ServerName will surface name of an instance of SQL Server that hosts an SSIS Catalog. The Execute Catalog Package Task will connect to this SQL Server instance to execute an SSIS package stored in the SSIS Catalog hosted on the instance named in the ServerName property.

Note the PackageCatalog property defaults to “SSISDB” and the other properties default the String.Empty. At the time of this writing, Microsoft limits the number of SSIS Catalogs to one catalog per SQL Server instance and defaults the name of this catalog to “SSISDB.” Although the PackageCatalog defaults to “SSISDB,” the value may be overridden if desired. Coding the task in this manner is an example of future-proofing, which means allowing for things to change in the future.

Non-defaulted property values are null. Null property values cannot be written (set) or read (get). Failure to initialize property values may present a troubleshooting challenge.

I prefer to leave the property read-write and recommend not removing the “set;” code from the declaration.

Investigating Task Methods

Earlier, we added inheritance to the ExecuteCatalogPackageTask class – public class ExecuteCatalogPackageTask : Microsoft.SqlServer.Dts.Runtime.Task – remember? When you inherit one class in another, the inherited class is identified as the base class. In Visual Basic, the base class may be directly addressed using the keyword MyBase; in C#, it’s base.

You can observe a bunch of information about a base class by clicking View ➤ Object Browser as shown in Figure 7-5:

Once open, navigate in Object Browser to the Microsoft.SqlServer.ManagedDTS Microsoft.SqlServer.ManagedDTS.RuntimeTask base class, as shown in Figure 7-6:

Methods contained in this class are listed on the right. If we select a method, we may view details in the description pane as shown in Figure 7-7:

When you add this method to the ExecuteCatalogPackageTask class, your code should appear as shown in Figure 7-8:

The InitializeTask method executes when the task is added to an SSIS Control Flow.

Overriding the Validate Method

The Validate method should be overridden in our class. Later in this book, we will implement validation code in this method. At this juncture, the custom task is not production-ready. A production-ready custom SSIS task should always implement validation. For now, stub this method with a DTSExecResult.Success return value.

After adding this code, your class should appear similar to that shown in Figure 7-9:

The Validate method is called when the task is added to the SSIS Control Flow, when a property changes, and when a task is executed. The line return DTSExecResult.Success tells the method to return Success – which translates to “I think I am ready to run” in Task-Speak – to the Control Flow when the method is invoked.

Overriding the Execute Method

As with the Validate method, we override the inherited Execute method with a bare minimum of functionality. Again, this is not yet production-ready code.

Altogether, your class should now contain three methods and resemble that shown in Figure 7-10:

That is all we are going to code in this task at this time. We will add more code to these methods later in this book. The additional code will make this custom SSIS task more production-ready.

Conclusion

The project now contains the beginnings of custom task functionality.

Now would be a good time to execute a commit and push to Azure DevOps.

We are now ready to begin coding the task interface.