Data Warehouse Architect
Sr. SQL Server DBA
Verisk Health, Nepal
Sr. Software Developer
Severstal NA, Greater Detroit Area
Unit testing allows developers, users, and package lifecycle administrators to ensure that a package is performing exactly what the developer implemented and handling unexpected circumstances with predefined behavior. Unit testing allows specified inputs to be used as "source" data and evaluates the task output with expected task output thereby ensuring the package behaves correctly.
From within the Pragmatic Workbench, click the Unit Test tile. This opens the Unit Test window.
Click the icon to ‘Add New Test Suite’. This will load all the stages of a unit test.
Right click ‘Packages’ to add a package that will be unit tested.
Next to ‘Add new package reference’ click the ellipses, browse to a package and click ‘Open’.
Click the ‘Connections’ link. Click the drop down menu next to ‘Connection Name’ and select the connection manager used by the parts of the package to be tested. Click ‘Add Connection’.
Click ‘Test Suite Setup’. In this window, you can set up certain commands that are run before the unit test starts. These commands are only run once.
Click the ‘T-SQL Command’ option. Select a connection that will be used to execute it. Add the T-SQL command in the SQL Query box below. In this example, a truncate statement will be executed. Click ‘Add Command’ at the bottom of the screen.
Multiple commands can be added here if needed. In this example I have also added a statement to update rows in a table.
This process can be repeated under the ‘Unit Test Setup’ link. The process is identical to ‘Test Suite Setup’, but these are commands that will run before each individual unit test.
Select ‘Unit Test’. Enter a name for the unit test and click ‘Add Unit Test’.
In the package drop down menu, select a package that contains the parts to be tested. In the ‘Task to Execute’ drop down menu, select the package name (to test the whole package) or the task within the package. Select the expected result from the menu below.
Below that, you can run commands called ‘Assertions’ to check the results from the unit test execution. Click the ‘Add Assert’ button.
Enter a name for the assert, and provide the expected result or value. Select the type of command that will be run. In this example, an assert will run a T-SQL command to check the number of rows that are moved by the package and another assert will check the average length of rows.
‘Unit Test Teardown’ and ‘Test Suite Teardown’ are just like the setup commands, but they are run after the unit test or unit test suite is completed.
To run the unit test, click the option to run selected test suites.
Test results are displayed upon completion.