SSIS:Automated Unit Testing

There are quite a few ways to perform unit testing for a SSIS package and also automate it.One such method is using the BizUnit Framework which is predominantly used for the Biz Unit testing.We can customize BizUnit to test SSIS Package as well.
We should write extension methods in BizUnit Framework to execute the SSIS package, query the database and compare the result with the expected result or even compare the output file with the expected output file.It might be a little extra effort for a SQL BI developer to do some c# coding but its an one time activity and definitely worth it.


Before going ahead let me create a simple package which would raise warnings in to system log table.



The package would have a script task like below

**Click on the Image for a clear view

**Click on the Image for a clear view

**Click on the Image for a clear view

Here using this package I am intending to check the warning message logged in the log table using the BizUnit.Alternatively you could also do a check on your output file or your destination table for the expected output.The warning message / error message related to the individual tasks can also be checked for negative testcase.

I have added the following to my existing solution

1)BizUnit.Extensions project which has the methods to compare the output with the expected output.
2)BizUnit.SsisSteps which has the methods to pass parameters and execute the SSIS packages.
3)SSIS.BizUnit.Test which has the test case xml and the test case method.

**Click on the Image for a clear view

After adding the Test project to the solution you can find the LocalTestRun.testrunconfig in the SolutionItems folder

**Click on the Image for a clear view

The SSIS package which needs to be tested and the folder where the input file/expected files (if required) should be added in the LocalTestRun.testrunconfig.
This is because while executing the Unit Testcase all the files associated with the test case will be copied to a test instance folder for execution.

My TestCase has the steps for the Unit Test execution defined in xml.Following image shows the steps in SSISPackageTest.xml

**Click on the Image for a clear view

The Test Setup is to delete the existing records in the System Log table . As you have noticed this implemented by calling the BizUnit.DatabaseDeleteStep extension .

The configurable values such as LogTable , Database connection are provided from Context in the Test method using the keyword "takeFromCtx"
Context is like an object which has all the configurable values required for the testmethod to execute .Each test method is associated with a Context where you can add new configurable values if required.

The TestExecutionStep executes the package using the extension assembly "BizUnit.SsisSteps.dll" and the method "BizUnit.SsisSteps.SsisPackageExecuterSteps"

As you could see I am comparing the message from the system log table with the expected error message in the Context.If they match then the Test case would pass.


My Test case method is shown below.

**Click on the Image for a clear view

The test methods are indicated by "[TestMethod]"
The test initialize method will be called during the start of the execution of the test methods.
The Context values respective to the test method are added with in the testmethod.The Context values which are common to all test methods are added in the test initialize.

A new instance of the BizUnit is created with the value SSISPackageTest.xml and the current context.

After creating the first Unit Test case you can view the unit test case in the TestListEditor.

**Click on the Image for a clear view

The result on execution of the test case is shown below.

**Click on the Image for a clear view

Similar to the example shown you could create several test cases for the following scenarios.

1)Testing the successful execution of the package
2)Comparing the expected warning message/error message in system log table by querying the db.
3)Comparing the expected output file with the expected output file.
4)Comparing the expected data in table by querying the db.


When the Unit test cases are moved to new environment only the Conext values should be changed in order to run the automated Test cases.