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

21. Testing the Task

Andy Leonard1  
(1)
Farmville, VA, USA
 

In previous chapters, we’ve coded features for the version of the Execute Catalog Package Task built in this second edition of the book. It’s now time to test the Execute Catalog Package Task.

Manually Testing Use Cases

Test frameworks that automate redundancies in test generation, execution, and reporting exist. If your job is developing software all day every day, you either know about test frameworks or you will. You will occasionally encounter a testing requirement for which your chosen test framework is either incapable or “clunky.” In that case, you will need to build a manual test.

All good testing is a series of scenarios, expected answers or results (assertions), and measurement of the actual results. The combination of a scenario and assertion is referred to as a use case. A single use case may contain several assertions. When we perform testing, we work through each use case, measure results, and record the measurements.

I have a maxim: “All software is tested. Some intentionally.” I occasionally post this maxim in social media, as shown in Figure 21-1:
../images/449652_2_En_21_Chapter/449652_2_En_21_Fig1_HTML.jpg
Figure 21-1

Andy’s software testing maxim

Software testing is meticulous and demanding work. We test some, not all, use cases in this chapter. If we tested all use cases, this chapter would be 150 pages, and my editor would (rightly) point out that this is a chapter, not a test plan.

Use Cases

In this section, we explore the following use cases for the Execute Catalog Package Task:
  1. 1.

    Task existence

     
  2. 2.

    Task validation

     
  3. 3.

    Task Editor – General

     

The first few use cases involve visual inspection – simply checking to see if the Execute Catalog Package Task appears in the SSIS Toolbox and, if so, selecting the Execute Catalog Package Task in the SSIS Toolbox and verifying the toolbox description.

The tests are documented using a manual testing documentation format. The author has seen similar manual testing documentation presented in an Excel spreadsheet and prefers Word documentation for manual test documentation.

Testing Task Existence

Scenario: Build the ExecuteCatalogPackageTask solution, open a test SSIS project, and then open a test SSIS package. The Execute Catalog Package Task should appear in the SSIS Toolbox, as shown in Figure 21-2:
../images/449652_2_En_21_Chapter/449652_2_En_21_Fig2_HTML.jpg
Figure 21-2

Does the Execute Catalog Package Task appear in the SSIS Toolbox?

Assertion: The Execute Catalog Package Task appears in the SSIS Toolbox.

Measurement: True.

Assertion: When the Execute Catalog Package Task is selected in the SSIS Toolbox, the Execute Catalog Package Task description appears in the Information portion of the SSIS Toolbox.

Measurement: True.

Testing Task Validation

Scenario: Drag an Execute Catalog Package Task to the Control Flow canvas of a test SSIS package. Examine the Error List dialog, as shown in Figure 21-3:
../images/449652_2_En_21_Chapter/449652_2_En_21_Fig3_HTML.jpg
Figure 21-3

Does the Execute Catalog Package Task appear – in error – on the Control Flow canvas?

Assertion: An Execute Catalog Package Task may be added to the Control Flow canvas of a test SSIS package.

Measurement: True.

Assertion: A newly added Execute Catalog Package Task raises validation errors.

Measurement: True.

Assertion: The validation errors raised by a newly added Execute Catalog Package Task are

  • Source Connection property is not properly configured.

  • SQL Server Instance Connection attempt failed.

Measurement: True.

Assertion: The earlier validation errors raised by a newly added Execute Catalog Package Task trigger an SSIS validation error:

  • There were errors during the task validation.

Measurement: True.

Testing the Task Editor

In the following test sections, we exercise the Execute Catalog Package Task Editor. Appearance and function are tested.

Scenario: Open the Execute Catalog Package Task Editor, as shown in Figure 21-4:
../images/449652_2_En_21_Chapter/449652_2_En_21_Fig4_HTML.jpg
Figure 21-4

Does the Execute Catalog Package Task Editor display without error?

Sometimes the solution build succeeds, and one (or more than one) value is misaligned between the ExecuteCatalogPackageTask and ExecuteCatalogPackageTaskComplexUI classes. When such misalignment occurs, the editor will not display. Instead, an error will display.

Assertion: The Execute Catalog Package Task Editor opens without error.

Measurement: True, the Execute Catalog Package Task Editor opens without error.

Assertion: The Execute Catalog Package Task Editor surfaces three Views:
  1. 1.

    General

     
  2. 2.

    Settings

     
  3. 3.

    Expressions

     

Measurement: True.

Assertion: The General View displays when the Execute Catalog Package Task Editor first opens.

Measurement: True.

Assertion: The General View displays the Execute Catalog Package Task Name and Description properties, and the property values are editable.

Measurement: True.

Scenario: Click the Settings view, as shown in Figure 21-5:
../images/449652_2_En_21_Chapter/449652_2_En_21_Fig5_HTML.jpg
Figure 21-5

Does the Execute Catalog Package Task Editor SettingsView display without error?

Assertion: The Settings View displays without error when Settings is selected.

Measurement: True.

Assertion: When Settings is selected, the Settings View displays the following categories and properties:
  • Connections

    • SourceConnection

  • SSIS Catalog Package Properties

    • Folder

    • Project

    • Package

  • SSIS Package Execution Properties

    • Use32bit

    • LoggingLevel

  • SSIS Package Synchronized Properties

    • Synchronized

    • MaximumRetries

    • RetryIntervalSeconds

    • OperationTimeoutMinutes

Measurement: True

Scenario: Click the SourceConnection property dropdown, as shown in Figure 21-6:
../images/449652_2_En_21_Chapter/449652_2_En_21_Fig6_HTML.jpg
Figure 21-6

Does the SourceConnection dropdown list display all available ADO.Net connection managers without error?

Assertion: The SourceConnection dropdown list displays all available ADO.Net connection managers without error.

Measurement: True.

Scenario: Configure the SourceConnection property value from the SourceConnection property dropdown, as shown in Figure 21-7:
../images/449652_2_En_21_Chapter/449652_2_En_21_Fig7_HTML.jpg
Figure 21-7

Selecting a value for the SourceConnection property

Scenario: Close the Execute Catalog Package Task Editor, as shown in Figure 21-8:
../images/449652_2_En_21_Chapter/449652_2_En_21_Fig8_HTML.jpg
Figure 21-8

Does the Execute Catalog Package Task appear – in error – on the Control Flow canvas?

Assertion: The validation errors raised by the Execute Catalog Package Task as currently configured are

  • There were errors during the task validation.

  • Folder property is not properly configured.

  • Value cannot be null.

Measurement: True.

Scenario: Open the Execute Catalog Package Task and navigate to the Settings view. Click the Folder property dropdown, as shown in Figure 21-9:
../images/449652_2_En_21_Chapter/449652_2_En_21_Fig9_HTML.jpg
Figure 21-9

Does the Folder dropdown list display all available folders in the SSIS Catalog configured in the SourceConnection without error?

Assertion: The Folder dropdown list displays all available folders in the SSIS Catalog configured in the SourceConnection property without error. To verify, close the Execute Catalog Package Task Editor, and edit the SSIS package connection manager selected in the SourceConnection property, as shown in Figure 21-10:
../images/449652_2_En_21_Chapter/449652_2_En_21_Fig10_HTML.jpg
Figure 21-10

Obtain the SQL Server instance from the SourceConnection connection manager

Use SQL Server Management Studio (SSMS) to connect to the SourceConnection SQL Server instance. Open SSMS Object Explorer and expand Integration Services Catalogs. Expand the SSISDB node and observe SSIS Catalog folders, as shown in Figure 21-11:
../images/449652_2_En_21_Chapter/449652_2_En_21_Fig11_HTML.jpg
Figure 21-11

Observing SSIS Catalog folders

The SSIS Catalog folders shown in Figure 21-11 match the list of SSIS Catalog folders shown in Figure 21-9.

Measurement: True.

Scenario: Configure the Folder property value from the Folder property dropdown, as shown in Figure 21-12:
../images/449652_2_En_21_Chapter/449652_2_En_21_Fig12_HTML.jpg
Figure 21-12

Selecting a value for the Folder property

Scenario: Close the Execute Catalog Package Task Editor, as shown in Figure 21-13:
../images/449652_2_En_21_Chapter/449652_2_En_21_Fig13_HTML.jpg
Figure 21-13

Does the Execute Catalog Package Task appear – in error – on the Control Flow canvas?

Assertion: The validation errors raised by the Execute Catalog Package Task as currently configured are

  • There were errors during the task validation.

  • Value cannot be null.

  • Project property is not properly configured.

Measurement: True.

Scenario: Open the Execute Catalog Package Task and navigate to the Settings view. Click the Project property dropdown, as shown in Figure 21-14:
../images/449652_2_En_21_Chapter/449652_2_En_21_Fig14_HTML.jpg
Figure 21-14

Does the Project dropdown list display all available projects in the SSIS Catalog folder configured in the Folder property without error?

Assertion: The Project dropdown list displays all available projects in the SSIS Catalog folder configured in the Folder property without error. To verify, use SQL Server Management Studio (SSMS) to connect to the SourceConnection SQL Server instance. Open SSMS Object Explorer and expand Integration Services Catalogs. Expand the SSISDB node, and then expand the SSIS Catalog folder configured in the Execute Catalog Package Task Folder property, as shown in Figure 21-15:
../images/449652_2_En_21_Chapter/449652_2_En_21_Fig15_HTML.jpg
Figure 21-15

Observing SSIS Catalog projects in the configured SSIS folder

The SSIS Catalog projects shown in Figure 21-15 match the list of SSIS Catalog projects shown in Figure 21-14.

Measurement: True.

Scenario: Configure the Project property value from the Project property dropdown, as shown in Figure 21-16:
../images/449652_2_En_21_Chapter/449652_2_En_21_Fig16_HTML.jpg
Figure 21-16

Selecting a value for the Project property

Scenario: Close the Execute Catalog Package Task Editor, as shown in Figure 21-17:
../images/449652_2_En_21_Chapter/449652_2_En_21_Fig17_HTML.jpg
Figure 21-17

Does the Execute Catalog Package Task appear – in error – on the Control Flow canvas?

Assertion: The validation errors raised by the Execute Catalog Package Task as currently configured are

  • There were errors during the task validation.

  • Value cannot be null.

  • Package property is not properly configured.

Measurement: True.

Scenario: Open the Execute Catalog Package Task and navigate to the Settings view. Click the Package property dropdown, as shown in Figure 21-18:
../images/449652_2_En_21_Chapter/449652_2_En_21_Fig18_HTML.jpg
Figure 21-18

Does the Package dropdown list display all available packages in the SSIS Catalog project configured in the Project property without error?

Assertion: The Package dropdown list displays all available packages in the SSIS Catalog project configured in the Project property without error. To verify, use SQL Server Management Studio (SSMS) to connect to the SourceConnection SQL Server instance. Open SSMS Object Explorer and expand Integration Services Catalogs. Expand the SSISDB node, expand the SSIS Catalog folder configured in the Execute Catalog Package Task Folder property, and then expand the SSIS Catalog project configured in the Execute Catalog Package Task Project property, as shown in Figure 21-19:
../images/449652_2_En_21_Chapter/449652_2_En_21_Fig19_HTML.jpg
Figure 21-19

Observing SSIS Catalog packages in the configured SSIS project

The SSIS Catalog packages shown in Figure 21-19 match the list of SSIS Catalog projects shown in Figure 21-18.

Measurement: True.

Scenario: Close the Execute Catalog Package Task Editor and view the Execute Catalog Package Task on the SSIS test package Control Flow, as shown in Figure 21-20:
../images/449652_2_En_21_Chapter/449652_2_En_21_Fig20_HTML.jpg
Figure 21-20

The Execute Catalog Package Task, minimally configured to pass validation

Assertion: No validation errors are raised by the Execute Catalog Package Task as currently configured.

Measurement: True.

Testing Execution

We shift gears at this point in the test process. While I dearly love manual software testing and documenting test results in “test-speak” for posterity, not everyone shares my passion. Starting here, we switch to prose.

Currently, the Execute Catalog Package Task is configured as shown in Figure 21-21:
../images/449652_2_En_21_Chapter/449652_2_En_21_Fig21_HTML.jpg
Figure 21-21

The Execute Catalog Package Task, as configured

The current configuration tests asynchronous SSIS package execution because the Synchronized property – which maps to the SSIS Catalog execution parameter – is set to false, which is the default.

Execute the test SSIS package. If all goes as planned, the test SSIS package with the Execute Catalog Package Task will succeed, as shown in Figure 21-22:
../images/449652_2_En_21_Chapter/449652_2_En_21_Fig22_HTML.jpg
Figure 21-22

A successful test execution

Observe the Progress/Execution Results tab to validate the instrumentation messages raised as events, as shown in Figure 21-23:
../images/449652_2_En_21_Chapter/449652_2_En_21_Fig23_HTML.jpg
Figure 21-23

Validating events

Test synchronous SSIS package execution by opening the Execute Catalog Package Task editor and updating the Synchronized property to True, as shown in Figure 21-24:
../images/449652_2_En_21_Chapter/449652_2_En_21_Fig24_HTML.jpg
Figure 21-24

Preparing to test synchronized execution

Click the OK button to close the Execute Catalog Package Task Editor, and execute the test SSIS package in the debugger. If all goes as planned, the test SSIS package with the Execute Catalog Package Task will succeed, as shown in Figure 21-25:
../images/449652_2_En_21_Chapter/449652_2_En_21_Fig25_HTML.jpg
Figure 21-25

Another successful test execution

View the Progress/Execution Results tab to validate asynchronous execution event messages are present, as shown in Figure 21-26:
../images/449652_2_En_21_Chapter/449652_2_En_21_Fig26_HTML.jpg
Figure 21-26

Asynchronous execution event messages: check

Configure the Execute Catalog Package Task to synchronously execute an SSIS package that fails, as shown in Figure 21-27:
../images/449652_2_En_21_Chapter/449652_2_En_21_Fig27_HTML.jpg
Figure 21-27

Preparing to test synchronized execution of an SSIS package built to fail

As expected, the SSIS package and Execute Catalog Package Task fail, as shown in Figure 21-28:
../images/449652_2_En_21_Chapter/449652_2_En_21_Fig28_HTML.jpg
Figure 21-28

A failed execution, as expected

Observe the Progress/Execution Results tab as shown in in Figure 21-29:
../images/449652_2_En_21_Chapter/449652_2_En_21_Fig29_HTML.jpg
Figure 21-29

Synchronous execution event messages, check

Configure the Execute Catalog Package Task to asynchronously execute the same SSIS package that fails, as shown in Figure 21-30:
../images/449652_2_En_21_Chapter/449652_2_En_21_Fig30_HTML.jpg
Figure 21-30

Preparing to test a non-synchronized execution of an SSIS package built to fail

As expected, the SSIS package and Execute Catalog Package Task succeed, as shown in Figure 21-31:
../images/449652_2_En_21_Chapter/449652_2_En_21_Fig31_HTML.jpg
Figure 21-31

A successful execution of a package, or is it?

Verify the instrumentation messages found on the Progress/Execution Results tab are accurate for asynchronous execution, as shown in Figure 21-32:
../images/449652_2_En_21_Chapter/449652_2_En_21_Fig32_HTML.jpg
Figure 21-32

Asynchronous execution instrumentation events

Examine the SSIS Catalog All Executions Report.

Configure a longer-running (greater than 30 seconds) SSIS package built to succeed. Configure the SSIS package to execute synchronously, as shown in Figure 21-33:
../images/449652_2_En_21_Chapter/449652_2_En_21_Fig33_HTML.jpg
Figure 21-33

Configuring a longer-running synchronous SSIS package execution

If all goes according to plan, the SSIS package and Execute Catalog Package Task succeed, as shown in Figure 21-34:
../images/449652_2_En_21_Chapter/449652_2_En_21_Fig34_HTML.jpg
Figure 21-34

Yet another successful execution

Observe the Progress/Execution Results to verify the instrumentation messages accurately reflect the progress of the execution, as shown in Figure 21-35:
../images/449652_2_En_21_Chapter/449652_2_En_21_Fig35_HTML.jpg
Figure 21-35

Event messages for a synchronous execution of a longer-running execution

Configure a longer-running (greater than 30 seconds) SSIS package built to fail. Configure the SSIS package to execute synchronously, as shown in Figure 21-36:
../images/449652_2_En_21_Chapter/449652_2_En_21_Fig36_HTML.jpg
Figure 21-36

Configuring a longer-running synchronous SSIS package built to fail

If all goes according to plan, the SSIS package and Execute Catalog Package Task fail, as shown in Figure 21-37:
../images/449652_2_En_21_Chapter/449652_2_En_21_Fig37_HTML.jpg
Figure 21-37

A failed execution, as expected

Verify the instrumentation messages found on the Progress/Execution Results tab are accurate for synchronous execution, as shown in Figure 21-38:
../images/449652_2_En_21_Chapter/449652_2_En_21_Fig38_HTML.jpg
Figure 21-38

Synchronous failed execution instrumentation events

Configure the Execute Catalog Package Task to asynchronously execute the same SSIS package that fails, as shown in Figure 21-39:
../images/449652_2_En_21_Chapter/449652_2_En_21_Fig39_HTML.jpg
Figure 21-39

Preparing to test a non-synchronized execution of the same SSIS package built to fail

As expected, the SSIS package and Execute Catalog Package Task succeed, as shown in Figure 21-40:
../images/449652_2_En_21_Chapter/449652_2_En_21_Fig40_HTML.jpg
Figure 21-40

A successful execution

Test timeout functionality by configuring a long-running SSIS package built to succeed. Set the Synchronized property to True, and then configure the MaximumRetries and RetryIntervalSeconds properties so that the SSIS package execution MaximumRetries value is reached before the SSIS package execution completes, as shown in Figure 21-41:
../images/449652_2_En_21_Chapter/449652_2_En_21_Fig41_HTML.jpg
Figure 21-41

Testing MaximumRetries

If all goes according to plan, the SSIS package and Execute Catalog Package Task fail, as shown in Figure 21-42:
../images/449652_2_En_21_Chapter/449652_2_En_21_Fig42_HTML.jpg
Figure 21-42

A failed execution, as expected

Examine the Progress/Execution Results tab to verify the correct event message – the event message indicating the SSIS package execution was canceled – displays, as shown in Figure 21-43:
../images/449652_2_En_21_Chapter/449652_2_En_21_Fig43_HTML.jpg
Figure 21-43

The SSIS Package execution was canceled

The next step is to verify that the SSIS package execution was canceled in the SSIS Catalog. Open SSMS and connect to the SQL Server instance that hosts the SSIS Catalog. Open the All Executions report to view the status of the SSIS package execution, as shown in Figure 21-44:
../images/449652_2_En_21_Chapter/449652_2_En_21_Fig44_HTML.jpg
Figure 21-44

The SSIS package execution status in the SSIS Catalog

If all goes according to plan, the All Executions SSIS Catalog report in SSMS should indicate the SSIS package execution was canceled.

Conclusion

In this chapter, we applied one manual testing technique for testing validation and then tested the Execute Catalog Package Task execution functionality.

The next step is to build an installer file to ease propagation of the Execute Catalog Package Task in an enterprise.

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