BI xPress - Workforce Logic
"BI xPress has accelerated our development project by enabling rich configurable auditing and notification to our hundreds of packages without any code changes."
C. O'Connor

Data Warehouse Architect

Workforce Logic

BI xPress - Tech Data
"BI xPress has saved us an incredible amount of SSIS development time and allowed us to create new across the board development standards..."
Dave F.

Sr. SQL Server DBA

Tech Data

BI xPress - Verisk Health
"It’s such a cool and functionable tool/product mainly designed for the SSIS field."
Anil Maharjan

Software Engineer

Verisk Health, Nepal

BI xPress - Severstal NA
"BIxPress has really helped us in our ETL and in streamlining our SSIS Packages.. This product is a thumbs up for anyone doing SSIS Packages."
Akhil Mahajan

Sr. Software Developer

Severstal NA, Greater Detroit Area

BI xPress - Peter Schott
"Finally got around to trial of BI xPress from Pragmatic Works - really wishing I'd done it a LOT sooner. Near real-time monitor==cool."
BI xPress - Gary M
"Use of tools such as BI xPress and DOC xPress from Pragmatic Works are relatively inexpensive tools that do amazing things with SSIS. BI xPress is a must for making notification more friendly and providing real-time and historical monitoring of ETL."


Buy Now Free Trial Download Compare Editions
BI xPress SSIS Unit Testing

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.

Feature Highlights:

  • Ensure package and / or task(s) execute as expected
  • Allow user to specify the scope of each unit test and it's expected output, if any
  • Show the results of the Unit Test within SSDT
SSIS Unit Testing

Unit Test - Step 1

From within the Pragmatic Workbench, click the Unit Test tile. This opens the Unit Test window.

From within the Pragmatic Workbench, click the Unit Test tile. This opens the Unit Test window.

Unit Test - Step 2

Click the icon to ‘Add New Test Suite’. This will load all the stages of a unit test.

Click the icon to ‘Add New Test Suite’. This will load all the stages of a unit test.

Unit Test - Step 3

Right click ‘Packages’ to add a package that will be unit tested.

Right click ‘Packages’ to add a package that will be unit tested.

Unit Test - Step 4

Next to ‘Add new package reference’ click the ellipses, browse to a package and click ‘Open’.

Next to ‘Add new package reference’ click the ellipses, browse to a package and click ‘Open’.

Unit Test - Step 5

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

Unit Test - Step 6

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

Unit Test - Step 7

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.

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.

Unit Test - Step 8

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.


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.


Unit Test - Step 9

Select ‘Unit Test’. Enter a name for the unit test and click ‘Add Unit Test’.

Select ‘Unit Test’. Enter a name for the unit test and click ‘Add Unit Test’.

Unit Test - Step 10

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.

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.

Unit Test - Step 11

Below that, you can run commands called ‘Assertions’ to check the results from the unit test execution. Click the ‘Add Assert’ button.

Below that, you can run commands called ‘Assertions’ to check the results from the unit test execution. Click the ‘Add Assert’ button.

Unit Test - Step 12

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. 

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 - Step 13

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

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

Unit Test - Step 14

To run the unit test, click the option to run selected test suites.

To run the unit test, click the option to run selected test suites.

Unit Test - Step 15

Test results are displayed upon completion. 

Test results are displayed upon completion. 

View Our Suite of Pragmatic Works Products
Copyright 2014 by Pragmatic Works