Starting the Unit Test
After clicking on the SSIS Unit test icon you will be greeted with the welcome page. From here click the Add New Test Suite Icon in the top left.
List and manage your package references.
Lists and manage your connection references.
List and manage your datasets.
Test Suite Setup/Teardown
Manage the commands that will run at the start and completion of the Test Suite (before all unit tests and after they complete).
Unit Test Setup/Teardown
Manage the commands that will run at the start and completion of each Unit Test. Enter your commands here if you have a set of commands you wish to run for each Unit Test to avoid having to create them inside the individual Unit Tests.
Manage your Unit Tests. Add package/connection references, create setups and teardowns that will run on the individual test, and create/manage your assertions.
Displays the results from the last test run, including which unit test was run, what the result was, and a time stamp of when the test was run. Your Connection References provide valid connection strings for your Test Suite. These connections can be added from Package References already added to the Test Suite, or entered manually.
From Package Reference
Click the "Refresh" button to load the package reference. The available connections from the project/package will be loaded in the drop down menu. Simply select the connection(s) you want to add to the Test Suite and click "Add Connection".
Select your provider from the drop down list.
Add a name for the connection and the connection string, and click "Add Connection." Your connection reference will now be available throughout the Test Suite.
Packages available to be tested
In this node, you create paths to the SSIS package(s) to be tested within your test suite. You can select your package from the file system, SQL Server, IS Catalog, Package Store, or from a 2012 compiled project*.
* If your packages use the Project Deployment Model, and contain project parameters and connections, you should choose the Compiled Project option.
Once you browse to your desired SSIS package, click "Add Package" to add it to the list of available package references.
This package is now available for use in your Unit Tests, and the connections in the package are available to be added to the Test Suite as Connection References.
Datasets are a way to compare two sets of data to each other
These are useful in your Unit Test Assertions, to ensure that data has been successfully added to a table, or to assert that two queries return the same results.
To begin, click "Add new Dataset."
Next, fill in the fields for Dataset Name, select your connection from the list of available Test Suite connections, and enter a valid query to test.
Datasets default to Live Query, which means that the dataset will execute the SQL Statement each time the Unit Test is run. You may also select "Cached" mode, which will allow you to execute the query once, save the results, and compare those results to other Datasets.
You must have both an expected Dataset and an actual Dataset, so at least two Datasets must be created.
How to test
After setting up your package references, connection references, and datasets (if necessary), it's time to create your Unit Test and test your package.
Create a new Unit Test
Give it a meaningful name, select the package reference to use, and choose what component to test. You may test the package in its entirety, or test only an individual Control Flow component.
Set the expected result of the test
If the component is expected to succeed or fail, choose that value here.
Add your Setups and Teardowns if needed, then specify an Assertion
Your assertions will need a name, expected result, and a command. In the example below, we are using a T-SQL Command to ensure that the result of the query matches our expected result of '6'.
Next, we run the tests
This can be done one of three ways: You can run an individual test, all of the unit tests without the Test Suite setups/teardowns, or run the Test Suite, which will execute all setups, teardowns, and unit tests. The results are then displayed in the Test Results node. You can review them to plan a strategy for correcting any failed tests and export the results to HTML or CSV file for reference.