© Andy Leonard 2021
A. LeonardBuilding Custom Tasks for SQL Server Integration Serviceshttps://doi.org/10.1007/978-1-4842-6482-9_13

13. Implement Views and Properties

Andy Leonard1  
(1)
Farmville, VA, USA
 

In Chapter 12, we built and tested a minimally coded ExecuteCatalogPackageTask sporting a new “SSIS-y” editor named ExecuteCatalogPackageTaskComplexUI. In this chapter, we will implement the IDTSTaskUIView editor interfaces for GeneralView and SettingsView, add editor properties, and conduct even more tests.

Implementing the GeneralView IDTSTaskUIView Interface

In Chapter 11, we used some nifty functionality built into Visual Studio 2019 to implement the required IDTSTaskUIView interface methods for the GeneralView with a single click, as shown in Figure 13-1:
../images/449652_2_En_13_Chapter/449652_2_En_13_Fig1_HTML.jpg
Figure 13-1

Implementing required IDTSTaskUIView interface methods for GeneralView

Near the end of the chapter, we commented out the throw statements so the ExecuteCatalogPackageTaskComplexUI project would build and pass some basic tests. The code now appears as shown in Listing 13-1:
public void OnInitialize(IDTSTaskUIHost treeHost, TreeNode viewNode, object taskHost, object connections)
  {
    // throw new NotImplementedException();
  }
public void OnValidate(ref bool bViewIsValid, ref string reason)
  {
    // throw new NotImplementedException();
  }
public void OnCommit(object taskHost)
  {
    // throw new NotImplementedException();
  }
public void OnSelection()
  {
    // throw new NotImplementedException();
  }
public void OnLoseSelection(ref bool bCanLeaveView, ref string reason)
  {
    // throw new NotImplementedException();
  }
Listing 13-1

IDTSTaskUIView interface methods for GeneralView, commented out

The code above has also been rearranged to more closely resemble an order I prefer (see the earlier note on “CDO”).

The next step is to implement the GeneralView OnInitialize method.

Implementing GeneralView OnInitialize

Implement the GeneralView OnInitialize method by replacing the current OnInitialize method code with the code in Listing 13-2:
public virtual void OnInitialize(IDTSTaskUIHost treeHost
                                , System.Windows.Forms.TreeNode viewNode
                                , object taskHost
                                , object connections)
{
  if (taskHost == null)
  {
throw new ArgumentNullException("Attempting to initialize the ExecuteCatalogPackageTask UI with a null TaskHost");
  }
  if (!(((TaskHost)taskHost).InnerObject is ExecuteCatalogPackageTask.ExecuteCatalogPackageTask))
  {
  throw new ArgumentException("Attempting to initialize the ExecuteCatalogPackageTask UI with a task that is not an ExecuteCatalogPackageTask.");
  }
  theTask = ((TaskHost)taskHost).InnerObject as ExecuteCatalogPackageTask.ExecuteCatalogPackageTask;
  this.generalNode = new GeneralNode(taskHost as TaskHost);
  generalPropertyGrid.SelectedObject = this.generalNode;
  generalNode.Name = ((TaskHost)taskHost).Name;
}
Listing 13-2

Implementing OnInitialize for GeneralView

The GeneralView OnInitialize method appears as shown in Figure 13-2:
../images/449652_2_En_13_Chapter/449652_2_En_13_Fig2_HTML.jpg
Figure 13-2

The GeneralView OnInitialize method

Note TaskHost and GeneralNode are marked with red squiggly lines to indicate an issue with each. Hover over TaskHost, click the dropdown, and then click “using Microsoft.SqlServer.Dts.Runtime;” as shown in Figure 13-3:
../images/449652_2_En_13_Chapter/449652_2_En_13_Fig3_HTML.jpg
Figure 13-3

Adding “using Microsoft.SqlServer.Dts.Runtime;”

This Visual Studio automation adds a using directive for the Microsoft.SqlServer.Dts.Runtime assembly reference, clearing a number of red squiggly lines in the GeneralView OnInitialize method, as shown in Figure 13-4:
../images/449652_2_En_13_Chapter/449652_2_En_13_Fig4_HTML.jpg
Figure 13-4

The using directive clears most design-time issues

Visual Studio line numbers are included in Figure 13-4 to aid in code functionality discussion. Lines 59–63 are the GeneralView OnInitialize method’s declaration and arguments.

Lines 64–67 check to see if the taskHost member is null and, if taskHost is null, throw an ArgumentNullException that includes the message: “Attempting to initialize the ExecuteCatalogPackageTask UI with a null TaskHost.”

Lines 69–72 test whether the GeneralView taskHost member’s InnerObject may not be cast to an instance of the ExecuteCatalogPackageTask type. If the GeneralView taskHost member’s InnerObject cannot be cast to an ExecuteCatalogPackageTask, the code throws an ArgumentException that includes the message: “Attempting to initialize the ExecuteCatalogPackageTask UI with a task that is not an ExecuteCatalogPackageTask.”

If the previous “type test” succeeds, the GeneralView theTask member’s InnerObject is assigned to the ExecuteCatalogPackageTask object on line 74.

On line 76, the GeneralView generalNode member is initialized (the code here is currently broken, but we will fix it soon).

On line 78, the GeneralView generalPropertyGrid member is initialized as the generalNode.

Finally, the generalNode.Name is initialized as the value of the taskHost.Name property value on line 80. The taskHost may be thought of as the “task on the SSIS package Control Flow.” This line of code helps keep the name of the ExecuteCatalogPackageTask in sync with the taskHost.Name property value displayed one the SSIS package Control Flow when the Execute Catalog Package Task editor is closed.

There are a lot of moving parts in the GeneralView OnInitialize method. A lot of what is occurring in the OnInitialize method is a knitting together of the SSIS task editor View ➤ Node ➤ Property Category ➤ Property hierarchy we originally discussed in Chapter 10.

An SSIS task editor surfaces properties in a hierarchy: View ➤ Node ➤ Property Category ➤ Property. Views are “pages” on an SSIS task editor. The following section appeared in Chapter 10. We present it here as a review.

Figure 13-5 visualizes the hierarchy for the Execute SQL Task Editor. As mentioned earlier, the General view (in the green box on the left) displays the General node – represented by the propertygrid control (in the blue box on the right). The property category named “General” is shown in the red box. The Name property is shown enclosed in the yellow box, as shown in Figure 13-5:
../images/449652_2_En_13_Chapter/449652_2_En_13_Fig5_HTML.jpg
Figure 13-5

Visualizing View ➤ Node ➤ Property Category ➤ Property

Applied to the View ➤ Node ➤ Property Category ➤ Property hierarchy, the names of the entities for the Execute SQL Task Editor – shown in Figure 13-5 – are the following: General (View) ➤ General (Node) ➤ General (Property Category) ➤ Name (Property). That’s a lot of General’s, and one – the General node – is hiding beneath the propertygrid control.

The new GeneralView OnInitialize method is coupling the ExecuteCatalogPackageTaskComplexUI to the ExecuteCatalogPackageTask using the GeneralView taskHost member. Once that succeeds – and the code will throw an exception if the coupling is not possible – the GeneralView’s generalNode member is initialized (to a new GeneralNode object) and the GeneralView’s generalPropertyGrid is, in turn, initialized to the (new) generalNode.

Again, there are a lot of moving parts in the GeneralView OnInitialize method.

Implementing GeneralView OnCommit

Implement the GeneralView OnCommit method by replacing the current OnCommit method code with the code in Listing 13-3:
public virtual void OnCommit(object taskHost)
{
  TaskHost th = (TaskHost)taskHost;
  th.Name = generalNode.Name.Trim();
  th.Description = generalNode.Description.Trim();
  theTask.TaskName = generalNode.Name.Trim();
  theTask.TaskDescription = generalNode.Description.Trim();
}
Listing 13-3

Implementing OnCommit for GeneralView

The GeneralView OnCommit method appears as shown in Figure 13-6:
../images/449652_2_En_13_Chapter/449652_2_En_13_Fig6_HTML.jpg
Figure 13-6

The GeneralView OnCommit method

When the Name and Description members are added to the GeneralNode class, the red squiggly lines in Figure 13-6 – along with the errors the indicate – will resolve. Add the TaskName and TaskDescription properties to the ExecuteCatalogPackageTask using the code in Listing 13-4 to resolve the red squiggly lines beneath the TaskName and TaskDescription properties to the ExecuteCatalogPackageTask:
public string TaskName { get; set; } = "Execute Catalog Package Task";
public string TaskDescription { get; set; } = "Execute Catalog Package Task";
Listing 13-4

Adding the TaskName and TaskDescription properties to the ExecuteCatalogPackageTask

Once added, the code appears as shown in Figure 13-7:
../images/449652_2_En_13_Chapter/449652_2_En_13_Fig7_HTML.jpg
Figure 13-7

Adding the TaskName and TaskDescription properties

Coding the GeneralNode

GeneralNode contains the ExecuteCatalogPackageTask properties surfaced on the General page of the Execute Catalog Package Task (complex) editor. Begin by adding members using the code in Listing 13-5:
internal TaskHost taskHost = null;
private ExecuteCatalogPackageTask.ExecuteCatalogPackageTask task = null;
Listing 13-5

Add taskHost and task members to GeneralNode

The GeneralNode class appears as shown in Figure 13-8:
../images/449652_2_En_13_Chapter/449652_2_En_13_Fig8_HTML.jpg
Figure 13-8

The GeneralNode class members

Add a constructor to the GeneralNode class by adding the code in Listing 13-6:
public GeneralNode(TaskHost taskHost)
{
  this.taskHost = taskHost;
  this.task = taskHost.InnerObject as ExecuteCatalogPackageTask.ExecuteCatalogPackageTask;
}
Listing 13-6

Adding a GeneralNode class constructor

Once the constructor is added, the GeneralNode class appears as shown in Figure 13-9:
../images/449652_2_En_13_Chapter/449652_2_En_13_Fig9_HTML.jpg
Figure 13-9

GeneralNode class constructor

After the GeneralNode constructor is implemented, the design-time error on line 76 clears, as shown in Figure 13-10:
../images/449652_2_En_13_Chapter/449652_2_En_13_Fig10_HTML.jpg
Figure 13-10

No more error

The next step is to code the GeneralNode properties.

Coding the GeneralNode Properties

In this section, we reach the Property Category ➤ Property portion of the View ➤ Node ➤ Property Category ➤ Property hierarchy. Properties of nodes are decorated to indicate property category and description. In the editor, the value of the property is paired with the property name, and this value is passed to the instantiation of the task. The pairing may be thought of as similar to a key-value mapping where the key is composed of the View ➤ Node ➤ Property Category ➤ Property hierarchy and the value is the value supplied by the SSIS developer during task (instance) edit. An example of a value is circled in Figure 13-11:
../images/449652_2_En_13_Chapter/449652_2_En_13_Fig11_HTML.jpg
Figure 13-11

The value of the key-value pair

In the SSIS package, the value is configured during editing by an SSIS developer. When the SSIS developer clicks the OK button on the editor, the OnCommit method fires and stores the key-value property configurations in the SSIS package’s XML, as shown in Figure 13-12:
../images/449652_2_En_13_Chapter/449652_2_En_13_Fig12_HTML.jpg
Figure 13-12

XML for an SSIS Execute SQL Task

In our sample Execute SQL Task, the Name property value is “Execute SQL Task.” The property value is stored in an attribute named DTS:ObjectName. At runtime, SSIS XML is loaded, interpreted, and executed, but it all starts with the SSIS developer opening the task editor and then configuring the value portion of key-value pairs, in which the keys are visually surfaced in editor views that make up the View ➤ Node ➤ Property Category ➤ Property hierarchy.

To add the Name property of our custom SSIS task, add the code in Listing 13-7 to the GeneralNode:
[
  Category("General"),
  Description("Specifies the name of the task.")
]
public string Name {
  get { return taskHost.Name; }
  set {
    if ((value == null) || (value.Trim().Length == 0))
    {
      throw new ApplicationException("Task name cannot be empty");
    }
    taskHost.Name = value;
  }
}
Listing 13-7

Adding the Name property to the GeneralNode

When added, the code appears as shown in Figure 13-13:
../images/449652_2_En_13_Chapter/449652_2_En_13_Fig13_HTML.jpg
Figure 13-13

Adding the Name property

The red squiggly lines under the decorations indicate an issue. Hovering over the code presents options to address the issue, as shown in Figure 13-14:
../images/449652_2_En_13_Chapter/449652_2_En_13_Fig14_HTML.jpg
Figure 13-14

Addressing the decoration issue

Adding the using directive resolves the issue (you have to love Visual Studio 2019!), as shown in Figure 13-15:
../images/449652_2_En_13_Chapter/449652_2_En_13_Fig15_HTML.jpg
Figure 13-15

The Name property with issues resolved

The Category and Description decorations shown on lines 117-120 are used by the propertygrid control for Property Category – which may hold one or more properties – and the Property Description displayed at the bottom of the editor when the property is selected during edit. The Property Category is set to General in this case; the Property Description is set to Specifies the name of the task. The Property Name is derived from the name of the view member – Name in this case.

Line 122 contains a get statement that returns the value of the Name property from the taskHost.Name property, or the Name property of the taskHost. While the statement is relatively short, there are a few moving parts. The GeneralNode taskHost member points back to the ExecuteCatalogPackageTask class. The taskHost.Name property value that the GeneralNode Name property is getting here is the ExecuteCatalogPackageTask.Name property value. Where is the ExecuteCatalogPackageTask.Name property value stored? In the SSIS package XML.

Lines 123–130 contain the set functionality for the property. Lines 124–127 check to see if the property value is null or empty and, if so, throws an ApplicationException that returns the message “Task name cannot be empty”.

If there is no exception, the value of the taskHost.Name is set on Line 129.

Add the Description property to the GeneralNode using the code in Listing 13-8:
[
  Category("General"),
  Description("Specifies the description for this task.")
]
public string Description {
  get { return taskHost.Description; }
  set { taskHost.Description = value; }
}
Listing 13-8

Adding the GeneralNode Description property

When added, the code appears as shown in Figure 13-16:
../images/449652_2_En_13_Chapter/449652_2_En_13_Fig16_HTML.jpg
Figure 13-16

The Description property

When the Name and Description properties are added, errors in the OnCommit method clear (compare to Figure 13-6), as shown in Figure 13-17:
../images/449652_2_En_13_Chapter/449652_2_En_13_Fig17_HTML.jpg
Figure 13-17

OnCommit errors cleared

Testing GeneralView

Build the solution and then open a test SSIS project. Add an Execute Catalog Package Task to the control flow and open the editor. Observe the General page, as shown in Figure 13-18:
../images/449652_2_En_13_Chapter/449652_2_En_13_Fig18_HTML.jpg
Figure 13-18

The GeneralView in action

The next step is to code the SettingsView.

Implementing the SettingsView IDTSTaskUIView Interface

In Chapter 11, we used the same nifty functionality built into Visual Studio 2019 to implement the required IDTSTaskUIView interface methods for the SettingsView as we used to implement the GeneralView interface, as shown in Figure 13-19:
../images/449652_2_En_13_Chapter/449652_2_En_13_Fig19_HTML.jpg
Figure 13-19

Implementing required IDTSTaskUIView interface methods for SettingsView

Near the end of the chapter, we commented out the throw statements so the ExecuteCatalogPackageTaskComplexUI project would build and pass some basic tests. The code – rearranged to suit my CDO – now appears as shown in Listing 13-9:
public void OnInitialize(IDTSTaskUIHost treeHost, TreeNode viewNode, object taskHost, object connections)
  {
    // throw new NotImplementedException();
  }
public void OnValidate(ref bool bViewIsValid, ref string reason)
  {
    // throw new NotImplementedException();
  }
public void OnCommit(object taskHost)
  {
    // throw new NotImplementedException();
  }
public void OnSelection()
  {
    // throw new NotImplementedException();
  }
public void OnLoseSelection(ref bool bCanLeaveView, ref string reason)
  {
    // throw new NotImplementedException();
  }
Listing 13-9

IDTSTaskUIView interface methods for SettingsView, commented out

The next step is to implement the SettingsView OnInitialize method.

Implementing SettingsView OnInitialize

Implement the SettingsView OnInitialize method by replacing the current OnInitialize method code with the code in Listing 13-10:
public virtual void OnInitialize(IDTSTaskUIHost treeHost
                               , System.Windows.Forms.TreeNode viewNode
                               , object taskHost
                               , object connections)
  {
    if (taskHost == null)
      {
        throw new ArgumentNullException("Attempting to initialize the ExecuteCatalogPackageTask UI with a null TaskHost");
      }
      if (!(((TaskHost)taskHost).InnerObject is ExecuteCatalogPackageTask.ExecuteCatalogPackageTask))
      {
        throw new ArgumentException("Attempting to initialize the ExecuteCatalogPackageTask UI with a task that is not a ExecuteCatalogPackageTask.");
      }
      theTask = ((TaskHost)taskHost).InnerObject as ExecuteCatalogPackageTask.ExecuteCatalogPackageTask;
      this.settingsNode = new SettingsNode(taskHost as TaskHost, connections);
      settingsPropertyGrid.SelectedObject = this.settingsNode;
  }
Listing 13-10

Implementing OnInitialize for SettingsView

As when coding the GeneralView, repair the missing using directive to clear the TaskHost design-time warning (see Figure 13-3). We will clear the SettingsNode design-time warning – the red squiggly line – later.

The SettingsView OnInitialize method should now appear as shown in Figure 13-20:
../images/449652_2_En_13_Chapter/449652_2_En_13_Fig20_HTML.jpg
Figure 13-20

The SettingsView OnInitialize method

Visual Studio line numbers are included in Figure 13-20 to aid in code functionality discussion. Lines 59–63 are the SettingsView OnInitialize method’s declaration and arguments.

Lines 64–67 check to see if the taskHost member is null and, if taskHost is null, throw an ArgumentNullException that includes the message: “Attempting to initialize the ExecuteCatalogPackageTask UI with a null TaskHost.”

Lines 69–72 test whether the SettingsView taskHost member’s InnerObject may not be cast to an instance of the ExecuteCatalogPackageTask type. If the SettingsView taskHost member’s InnerObject cannot be cast to an ExecuteCatalogPackageTask, the code throws an ArgumentException that includes the message: “Attempting to initialize the ExecuteCatalogPackageTask UI with a task that is not an ExecuteCatalogPackageTask.”

If the previous “type test” succeeds, the SettingsView theTask member’s InnerObject is assigned to the ExecuteCatalogPackageTask object on line 74.

On line 76, the SettingsView generalNode member is initialized (the code here is currently broken, but we will fix it soon).

Finally, the SettingsView settingsPropertyGrid member is initialized as the settingsNode.

As with the GeneralView, there are a lot of moving parts in the SettingsView OnInitialize method. The new SettingsView OnInitialize method is coupling the ExecuteCatalogPackageTaskComplexUI to the ExecuteCatalogPackageTask using the SettingsView taskHost member. Once that succeeds – and the code will throw an exception if the coupling is not possible – the SettingsView’s settingsNode member is initialized (to a new SettingsNode object), and the SettingsView’s settingsPropertyGrid is, in turn, initialized to the (new) settingsNode.

Adding ExecuteCatalogPackageTask Properties

Before implementing SettingsView OnCommit, we need to add a few properties to the ExecuteCatalogPackageTask class . Open the ExecuteCatalogPackageTask.cs file in the ExecuteCatalogPackageTask project, and then add the member declarations in Listing 13-11:
public string ConnectionManagerName { get; set; } = String.Empty;
public bool Synchronized { get; set; } = false;
public bool Use32bit { get; set; } = false;
public string LoggingLevel { get; set; } = "Basic";
Listing 13-11

Adding ExecuteCatalogPackageTask members

When added, the new ExecuteCatalogPackageTask members appear as shown in Figure 13-21:
../images/449652_2_En_13_Chapter/449652_2_En_13_Fig21_HTML.jpg
Figure 13-21

New ExecuteCatalogPackageTask members

SettingsView surfaces the ExecuteCatalogPackageTask properties configured on the Settings page of the Execute Catalog Package Task (complex) editor. In this section, we introduce complexity into the complex editor, so we will take an incremental approach to coding, building, and testing. We begin by adding the FolderName, ProjectName, and PackageName properties.

Implementing SettingsView OnCommit for FolderName, ProjectName, and PackageName Properties

Return to the ExecuteCatalogPackageTaskComplexUI project and implement the SettingsView OnCommit method by replacing the current OnCommit method code with the code in Listing 13-12:
public virtual void OnCommit(object taskHost)
  {
    theTask.PackageFolder = settingsNode.FolderName;
    theTask.PackageProject = settingsNode.ProjectName;
    theTask.PackageName = settingsNode.PackageName;
  }
Listing 13-12

Implementing OnCommit for SettingsView

The SettingsView OnCommit method appears as shown in Figure 13-22:
../images/449652_2_En_13_Chapter/449652_2_En_13_Fig22_HTML.jpg
Figure 13-22

The SettingsView OnCommit method for FolderName, ProjectName, and PackageName Properties

The next step is to code SettingsNode for the FolderName, ProjectName, and PackageName properties.

Coding SettingsNode for FolderName, ProjectName, and PackageName Properties

Begin by adding FolderName, ProjectName, and PackageName members to SettingsNode using the code in Listing 13-13:
internal ExecuteCatalogPackageTask.ExecuteCatalogPackageTask _task = null;
private TaskHost _taskHost = null;
Listing 13-13

Add members to SettingsNode

The SettingsNode class appears as shown in Figure 13-23:
../images/449652_2_En_13_Chapter/449652_2_En_13_Fig23_HTML.jpg
Figure 13-23

The SettingsNode class members

Add a constructor to the SettingsNode class by adding the code in Listing 13-14:
public SettingsNode(TaskHost taskHost
                  , object connections)
  {
    _taskHost = taskHost;
    _task = taskHost.InnerObject as ExecuteCatalogPackageTask.ExecuteCatalogPackageTask;
  }
Listing 13-14

Adding a SettingsNode class constructor

Once the constructor is added, the SettingsNode class appears as shown in Figure 13-24:
../images/449652_2_En_13_Chapter/449652_2_En_13_Fig24_HTML.jpg
Figure 13-24

SettingsNode class constructor

After the SettingsNode constructor implementation, the design-time warning on line 78 clears, as shown in Figure 13-25:
../images/449652_2_En_13_Chapter/449652_2_En_13_Fig25_HTML.jpg
Figure 13-25

No more error

The next step is to code the SettingsNode FolderName, ProjectName, and PackageName properties.

Coding the SettingsNode FolderName, ProjectName, and PackageName Properties

Let’s begin with SettingsNode members (properties) that identify the package in the SSIS Catalog we wish to execute:

  • Folder name

  • Project name

  • Package name

To add the FolderName, ProjectName, and PackageName properties of our custom SSIS task, add the code in Listing 13-15 to the SettingsNode:
[
  Category("SSIS Catalog Package Properties"),
  Description("Enter SSIS Catalog Package folder name.")
]
public string FolderName {
  get { return _task.PackageFolder; }
  set {
        if (value == null)
        {
          throw new ApplicationException("Folder name cannot be empty");
        }
        _task.PackageFolder = value;
      }
}
[
  Category("SSIS Catalog Package Properties"),
  Description("Enter SSIS Catalog Package project name.")
]
public string ProjectName {
  get { return _task.PackageProject; }
  set {
        if (value == null)
        {
          throw new ApplicationException("Project name cannot be empty");
        }
        _task. PackageProject = value;
      }
}
[
  Category("SSIS Catalog Package Properties"),
  Description("Enter SSIS Catalog Package name.")
]
public string PackageName {
  get { return _task.PackageName; }
  set {
        if (value == null)
        {
          throw new ApplicationException("Package name cannot be empty");
        }
        _task. PackageName = value;
      }
}
Listing 13-15

Adding FolderName, ProjectName, and PackageName properties to the SettingsNode

When added, the code appears as shown in Figure 13-26:
../images/449652_2_En_13_Chapter/449652_2_En_13_Fig26_HTML.jpg
Figure 13-26

Adding FolderName, ProjectName, and PackageName properties

Note the value is checked for null, but not checked for 0 length. The reason for the omission of a 0-length string check is the code will eventually set the value of the FolderName, ProjectName, and PackageName properties to an empty string when “parent” properties are updated in the editor. For example, if the SSIS developer selects a new ProjectName property value, the code needs to set the current PackageName property value to an empty string. This functionality will be coded a few chapters hence.

As with adding initial properties to the GeneralNode, adding decorations requires adding the using System.ComponentModel; directive to the SettingsView.cs file.

FolderName, ProjectName, and PackageName SettingsNode members are validated by design time much the same way GeneralNode members were validated.

Testing SettingsView FolderName, ProjectName, and PackageName Properties

Build the solution and then open a test SSIS project. Add an Execute Catalog Package Task to the control flow and open the editor. Observe the Settings page, as shown in Figure 13-27:
../images/449652_2_En_13_Chapter/449652_2_En_13_Fig27_HTML.jpg
Figure 13-27

The SettingsView FolderName, ProjectName, and PackageName properties in action

The next step is to add connection-related properties to SettingsNode.

Coding SettingsNode for Connection-Related Members

To begin adding connection-related members to the SettingsNode, add a private member named _connections (of the object type) to the SettingsNode class using the code in Listing 13-16:
private object _connections = null;
Listing 13-16

Adding the _connections object to SettingsNode

Once added, SettingsNode members should appear as shown in Figure 13-28:
../images/449652_2_En_13_Chapter/449652_2_En_13_Fig28_HTML.jpg
Figure 13-28

Adding _connections

In the SettingsNode constructor, initialize the _connections object to the connections object passed to the constructor by adding the code in Listing 13-17:
_connections = connections;
Listing 13-17

Initializing the _connections member

The SettingsNode constructor appears as shown in Figure 13-29:
../images/449652_2_En_13_Chapter/449652_2_En_13_Fig29_HTML.jpg
Figure 13-29

Connections, initialized

The next step is isolating ADO.Net connections contained within the SSIS package.

Regarding SSIS Package Connections

The next few paragraphs are not intended to confuse you, but they may confuse you if you are reading about how SSIS packages surface collections to SSIS tasks for the very first time. The following statements are true about SSIS:

  • SSIS packages and containers are containers.

  • SSIS packages, containers, and tasks are executables.

  • An SSIS package will always contain a collection of containers – and the containers collection will always have at least one member because the package itself is a container.

  • An SSIS package will always contain a collection of executables – and the executables collection will always have at least one member because the package itself is an executable.

  • An SSIS package will always contain a collection of connection managers – and the connections collection may contain 0, 1, or several connection managers.

The SSIS package connections collection is available to executables in case any given executable needs to consume an SSIS package connection manager. SSIS project connection managers are also members of SSIS package connections collections.

When the ExecuteCatalogPackageTaskComplexUIForm is instantiated, the ExecuteCatalogPackageTaskComplexUI’s GetView method is called, and a member named connectionService (of the IDtsConnectionService type) is passed to the SettingsView OnInitialize method. The connections collection is passed to the SettingsNode constructor, where it may be used to populate lists of certain connection types.

Isolating SSIS Package ADO.Net Connections

To begin, add a Connections member to the SettingsNode using the code in Listing 13-18:
[
  Browsable(false)
]
internal object Connections {
  get { return _connections; }
  set { _connections = value; }
}
Listing 13-18

Adding a Connections object to SettingsNode

The SettingsNode Connections property is different from previous properties in two ways:

  • Connections is an object type.

  • The member decoration is Browsable(false), which means the Connections member is a property, but the Connections property is not surfaced in the SettingsNode propertygrid.

When added to the SettingsNode, the Connections property appears as shown in Figure 13-30:
../images/449652_2_En_13_Chapter/449652_2_En_13_Fig30_HTML.jpg
Figure 13-30

The Connections property

We want to only connect to the SSIS Catalog using an ADO.Net connection. To acquire a list of ADO.Net connection managers included in the SSIS package, add the code in Listing 13-19 to the SettingsNode constructor:
_connections = ((IDtsConnectionService)connections).GetConnectionsOfType("ADO.Net");
Listing 13-19

Acquiring a list of ADO.Net connection managers

When added to the project, the code appears as shown in Figure 13-31:
../images/449652_2_En_13_Chapter/449652_2_En_13_Fig31_HTML.jpg
Figure 13-31

Adding the list of ADO.Net connection managers to _connections

We now have a list of ADO.Net connection managers stored in the _connections object, which are hidden (not Browsable), internal SettingsNode property (member) named Connections.

The next step is to build a TypeConverter that builds a list of ADO.Net connection manager names.

Building the ADONetConnections TypeConverter

A TypeConverter “provides a unified way of converting types of values to other types, as well as for accessing standard values and subproperties,” according to Microsoft’s TypeConverter Class documentation (docs.microsoft.com/en-us/dotnet/api/system.componentmodel.typeconverter?view=netframework-4.7.2). A TypeConverter is used to create an enumeration, or list, of objects from a collection. Applied to the list of ADO.Net connection managers in the ExecuteCatalogPackageTaskComplexUI, we will build an ArrayList of string values containing the names of ADO.Net connection managers stored in the collection of Connections (of the object type).

To begin, add a new class named ADONetConnections to the SettingsView.cs file using the code in Listing 13-20:
internal class ADONetConnections : StringConverter { }
Listing 13-20

Adding the ADONetConnections class

When added, the code will appear as shown in Figure 13-32:
../images/449652_2_En_13_Chapter/449652_2_En_13_Fig32_HTML.jpg
Figure 13-32

The new ADONetConnections StringConverter class

StringConverter is one interface for TypeConverter.

Declare and initialize a constant string member named NEW_CONNECTION to the ADONetConnections class using the code in Listing 13-21:
private const string NEW_CONNECTION = "<New Connection...>";
Listing 13-21

Adding NEW_CONNECTION to ADONetConnections

ADONetConnections should appear as shown in Figure 13-33 after declaring the new member:
../images/449652_2_En_13_Chapter/449652_2_En_13_Fig33_HTML.jpg
Figure 13-33

Declaring ADONetConnections.NEW_CONNECTION

A TypeConverter follows a general pattern, surfacing one object and three overridable methods:

  • GetSpecializedObject (object)

  • GetStandardValues (StandardValuesCollection)

  • GetStandardValuesExclusive (bool)

  • GetStandardValuesSupported (bool)

Implement these methods by adding the code in Listing 13-22 to the ADONetConnections class:
private object GetSpecializedObject(object contextInstance)
  {
    DTSLocalizableTypeDescriptor typeDescr = contextInstance as DTSLocalizableTypeDescriptor;
    if (typeDescr == null)
    {
      return contextInstance;
    }
    return typeDescr.SelectedObject;
  }
public override StandardValuesCollection GetStandardValues(ITypeDescriptorContext context)
  {
    object retrievalObject = GetSpecializedObject(context.Instance) as object;
   return new StandardValuesCollection(getADONetConnections(retrievalObject));
  }
public override bool GetStandardValuesExclusive(ITypeDescriptorContext context)
  {
    return true;
  }
public override bool GetStandardValuesSupported(ITypeDescriptorContext context)
  {
    return true;
  }
Listing 13-22

Adding TypeConverter methods to ADONetConnections

When added, the code appears as shown in Figure 13-34:
../images/449652_2_En_13_Chapter/449652_2_En_13_Fig34_HTML.jpg
Figure 13-34

Adding ADONetConnections methods

A discussion of TypeConverter methods is an in-depth topic that the author chooses not to include in this example. The outcome of the TypeConverter implemented in the ADONetConnections StringConverter will be an ArrayList containing the names of ADO.Net connection managers configured in the SSIS package, plus the option to create a new ADO.Net connection manager.

The ADONetConnections StringConverter‘s GetStandardValues method calls getADONetConnections, which builds and returns the ArrayList, using the code in Listing 13-23:
private ArrayList getADONetConnections(object retrievalObject)
  {
    SettingsNode node = (SettingsNode)retrievalObject;
    ArrayList list = new ArrayList();
    ArrayList listConnections = new ArrayList();
    listConnections = (ArrayList)node.Connections;
    // adds the new connection item
    list.Add(NEW_CONNECTION);
    // adds each ADO.Net connection manager
    foreach (ConnectionManager cm in listConnections)
    {
      list.Add(cm.Name);
    }
    // sorts the connection manager list
    if ((list != null) && (list.Count > 0))
    {
      list.Sort();
    }
    return list;
  }
Listing 13-23

getADONetConnections

Clear the ArrayList type errors by adding the directive using System.Collections; to the SettingsView.cs file.

Once added, the code will appear as shown in Figure 13-35:
../images/449652_2_En_13_Chapter/449652_2_En_13_Fig35_HTML.jpg
Figure 13-35

getADONetConnections added

A brief description of the code in getADONetConnections follows. An instance of settingsNode is passed to the getADONetConnections method in the retrievalObject (object) argument. A new internal SettingsNode variable named node and two ArrayList variables – list and listConnections – are declared and initialized on lines 244–246. listConnections is assigned the Connections object of the SettingsNode variable (node) on line 248. NEW_CONNECTION is added to the list (ArrayList) on line 251.

On lines 254–257, a foreach loop enumerates each connection in listConnections and adds the name of each connection manager to the list ArrayList. Remember, per the SettingsNode constructor, Connections contains only ADO.Net connection managers.

If the list ArrayList contains values, the code on lines 260–263 sorts the values contained in the list ArrayList.

On line 265, the list ArrayList is returned to the caller.

Surface the SourceConnection Property

The work in this section titled “Coding SettingsNode for Connection-Related Members” has all led to this point where we are (finally) able to add the SourceConnection property to SettingsView.

To surface the SourceConnection property, add the code in Listing 13-24 to the SettingsNode class:
[
  Category("Connections"),
  Description("The SSIS Catalog connection"),
  TypeConverter(typeof(ADONetConnections))
]
public string SourceConnection {
  get { return _task.ConnectionManagerName; }
  set { _task.ConnectionManagerName = value; }
  }
Listing 13-24

Adding the SourceConnection property

When added the code appears as shown in Figure 13-36:
../images/449652_2_En_13_Chapter/449652_2_En_13_Fig36_HTML.jpg
Figure 13-36

SourceConnection member added to SettingsNode

Testing the SourceConnection Property

Build the solution. If all goes well, the solution should build successfully. Open a test SSIS project and add the Execute Catalog Package Task to the control flow of a test SSIS package. Open the editor and click on the Settings page. The SourceConnection property appears in the Connections category and contains a list of the names of ADO.Net connection managers in the SSIS packages – plus the option to create a new connection – as shown in Figure 13-37:
../images/449652_2_En_13_Chapter/449652_2_En_13_Fig37_HTML.jpg
Figure 13-37

The SourceConnection property is visible and populated

SourceConnection surfaces a dropdown list because the SettingsNode SourceConnection property decoration includes the TypeConverter attribute, informing the SettingsView PropertyGrid that the values in the SourceConnection property are of the ADONetConnections type. The ADONetConnections type is a StringConverter that contains an ArrayList of strings, and the list of strings the ArrayList contains is the names of all ADO.Net connection managers in the connections collection of the SSIS package.

Reaching this point in development is awesome: We’ve surfaced the SourceConnection property! That’s the good news. The bad news is the SourceConnection property doesn’t do anything – yet. We are almost there, though.

The next step is to add code so the Execute Catalog Package Task uses the SourceConnection property.

Now would be an excellent time to check in your code.

Using the SourceConnection Property

Update the SettingsView OnCommit method to send the SettingsNode SourceConnection member value to the ExecuteCatalogPackageTask’s ConnectionManagerName property by adding the code in Listing 13-25 to the SettingsView OnCommit method:
theTask.ConnectionManagerName = settingsNode.SourceConnection;
Listing 13-25

Assigning the ExecuteCatalogPackageTask’s ConnectionManagerName property

Once added, the OnCommit method appears as shown in Figure 13-38:
../images/449652_2_En_13_Chapter/449652_2_En_13_Fig38_HTML.jpg
Figure 13-38

ConnectionManagerName property, assigned

The OnCommit method fires when the view commits. In the case of the SettingsView – at this point during development – the SourceConnection, FolderName, ProjectName, and PackageName properties managed by the settingsNode will set the values of the ExecuteCatalogPackageTask’s ConnectionManagerName, PackageFolder, PackageProject, and PackageName properties, respectively.

Extracting the ServerName from the SourceConnection

SourceConnection contains the name of an SSIS package connection manager (an ADO.Net connection manager). We may extract the name of the SQL Server instance contained in the connection manager’s connection string using the returnSelectedConnectionManagerDataSourceValue function code in Listing 13-26:
private string returnSelectedConnectionManagerDataSourceValue(string connectionManagerName, string connectionString = "")
  {
    string ret = String.Empty;
    ArrayList listConnections = (ArrayList)settingsNode.Connections;
    string connString = String.Empty;
    // match the selected ADO.Net connection manager
    if (listConnections.Count > 0)
    {
      foreach (ConnectionManager cm in listConnections)
      {
        if (cm.Name == connectionManagerName)
        {
          connString = cm.ConnectionString;
        }
      }
    }
    else
    {
      connString = connectionString;
    }
    // parse if a match is found
    if (connString!= String.Empty)
      {
        string dataSourceStartText = "Data Source=";
        string dataSourceEndText = ";";
        int dataSourceTagStart = connString.IndexOf(dataSourceStartText) + dataSourceStartText.Length;
        int dataSourceTagEnd = 0;
        int dataSourceTagLength = 0;
        if (dataSourceTagStart > 0)
        {
          dataSourceTagEnd = connString.IndexOf(dataSourceEndText, dataSourceTagStart);
          if (dataSourceTagEnd > dataSourceTagStart)
          {
            dataSourceTagLength = dataSourceTagEnd - dataSourceTagStart;
            ret = connString.Substring(dataSourceTagStart, dataSourceTagLength);
          }
        }
      }
      return ret;
}
Listing 13-26

The returnSelectedConnectionManagerDataSourceValue function

When added to the SettingsView.cs file, the code appears as shown in Figure 13-39:
../images/449652_2_En_13_Chapter/449652_2_En_13_Fig39_HTML.jpg
Figure 13-39

The returnSelectedConnectionManagerDataSourceValue function

There are other ways to extract the Data Source attribute value from a connection string. Feel free to implement a better way in your code.

Update the SettingsView OnCommit method to send the value of the server name contained in the SettingsNode SourceConnection ConnectionString’s Data Source attribute value to the ExecuteCatalogPackageTask’s ServerName property by adding the code in Listing 13-27 to the SettingsView OnCommit method :
theTask.ServerName = returnSelectedConnectionManagerDataSourceValue(settingsNode.SourceConnection);
Listing 13-27

Assigning the ExecuteCatalogPackageTask’s ServerName property

Once added, the OnCommit method appears as shown in Figure 13-40:
../images/449652_2_En_13_Chapter/449652_2_En_13_Fig40_HTML.jpg
Figure 13-40

ServerName property, assigned

Finally, edit the SettingsView.propertyGridSettings_PropertyValueChanged method so that changes to the SourceConnection property also trigger a call to the returnSelectedConnectionManagerDataSourceValue function – in two places (first, inside the New Connection functionality after assigning the SettingsNode.SourceConnection; second, add an else to the if (e.ChangedItem.Value.Equals(NEW_CONNECTION)) and place a call there) – using the code in Listing 13-28:
if (e.ChangedItem.PropertyDescriptor.Name.CompareTo("SourceConnection") == 0)
{
  if (e.ChangedItem.Value.Equals(NEW_CONNECTION))
  {
    ArrayList newConnection = new ArrayList();
    if (!((settingsNode.SourceConnection == null)
       || (settingsNode.SourceConnection == "")))
    {
      settingsNode.SourceConnection = null;
    }
    newConnection = connectionService.CreateConnection("ADO.Net");
    if ((newConnection != null) && (newConnection.Count > 0))
    {
      ConnectionManager cMgr = (ConnectionManager)newConnection[0];
      settingsNode.SourceConnection = cMgr.Name;
      theTask.ServerName = returnSelectedConnectionManagerDataSourceValue(settingsNode.SourceConnection , cMgr.ConnectionString);
      settingsNode.Connections = connectionService.GetConnectionsOfType("ADO.Net");
    }
    else
    {
      if (e.OldValue == null)
      {
        settingsNode.SourceConnection = null;
      }
      else
      {
        settingsNode.SourceConnection = (string)e.OldValue;
      }
    }
  }
  else
  {
    theTask.ServerName = returnSelectedConnectionManagerDataSourceValue(settingsNode.SourceConnection);
  }
}
Listing 13-28

Edit the response to SourceConnection property value changes

Once added, the code appears as shown in Figure 13-41:
../images/449652_2_En_13_Chapter/449652_2_En_13_Fig41_HTML.jpg
Figure 13-41

Updated response to SourceConnection property value changes

Let’s Test It!

Build the solution. If all goes well, the solution should build successfully. Open a test SSIS project and add an ADO.Net connection manager aimed at an SSIS Catalog. Add the Execute Catalog Package Task to the control flow of a test SSIS package. Open the editor and click on the Settings page. Select the SSIS Catalog Ado.Net connection manager from the SourceConnection property dropdown.

Configure the SSIS Catalog FolderName, ProjectName, and PackageName properties – setting them to match a folder, project, and package in your SSIS Catalog, as shown in Figure 13-42:
../images/449652_2_En_13_Chapter/449652_2_En_13_Fig42_HTML.jpg
Figure 13-42

Configuring the Execute Catalog Package Task

Click the OK button to close the editor and then execute the SSIS package. If all goes as planned, the SSIS package execution succeeds. Open SQL Server Management Studio (SSMS) and connect to the SSIS Catalog’s All Executions report. Verify the SSIS package executed, as shown in Figure 13-43:
../images/449652_2_En_13_Chapter/449652_2_En_13_Fig43_HTML.jpg
Figure 13-43

Successful execution

We’re not done coding SourceConnection functionality, but we have reached parity with the previous editor’s functionality.

Conclusion

In this chapter, we implemented the IDTSTaskUIView editor interfaces for GeneralView and SettingsView, added properties (members) to each view, and conducted tests – where possible – each step along the way.

The next steps are completing the SourceConnection property functionality and adding more SettingsView members.

Now would be an excellent time to check in your code.

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
3.145.15.1