In this article, I will present a simple ETL application scenario that will be used to demonstrate the SSISUnit unit testing framework in action.
SSISUnit is an open source unit testing framework for SQL Server Integration Services, developed and maintained by Pragmatic Works. You can find more details about it at CodePlex.
There are several ways to use SSISUnit:
a) via the GUI Windows Application
b) via the Command Line Application
c) via the .Net API
Options a) and b) uses XML files to store the test definitions, whilst option c) offers the most flexibility of using the API directly or in combination with XML definition files. In addition option c) is the method that allows us to seamlessly integrate SSISUnit with the Visual Studio unit testing framework.
The Visual Studio unit testing framework offers many features for developer focused unit testing all within one single Integrated Development Environment. For more information about the Visual Studio unit testing framework see this MSDN site.
The Visual Studio unit testing framework offers many features for developer focused unit testing all within one single Integrated Development Environment. For more information about the Visual Studio unit testing framework see this MSDN site.
I am going show you how I have integrated SSISUnit with the Visual Studio unit testing framework via option c) along with XML test definitions.
Development environment
First of all let me give you the details of the software I use and my development environment.
a) SQL Server 2012 Developer with Service Pack 3 running on a Windows 10 desktop (on a LAN)
b) Visual Studio 2012 Ultimate with update 5 running on a Windows 7 laptop (on a LAN)
c) SQL Server Data Tools 2012 (version 11.1) for Visual Studio
d) SQL Server Data Tools with Business Intelligence (version 11..0) for Visual Studio 2012
I have only developed this test application on the above specification, so I cant guarantee it working with other specifications. However SSISUnit is also available from CodePlex for the following environments:
* Visual Studio 2010 targeting SQL Server 2008
* Visual Studio 2013 targeting SQL Server 2014
Setup SSISUnit
Downloaded the source code from CodePlex. The download is a zip file containing several solutions for the following versions of Visual Studio.
SSISUnit_SQL2008.sln :- Visual Studio 2010 targeting SQL Server 2008
SSISUnit_SQL2012.sln :- Visual Studio 2012 targeting SQL Server 2012
SSISUnit_SQL2014.snl :- Visual Studio 2013 targeting SQL Server 2014
The solution I have used for this work is SSISUnit_SQL2012.snl. The solution contains several projects including one for the GUI application, console application and API libraries. The project file containing the API is SSISUnit_SQL2012.csproj. Note that the target framework for this project is .Net Framework 4. So ensure that this framework has been installed on the development environment first. Building this solution successfully will produce the .dll files required to use the SSISUnit API. Please note the following .dll files and their locations which will be referenced by the test application later.
<extract folder>\MAIN\SSISUnit\bin\Release\SSISUnit2012.dll
<extract folder>\MAIN\SSISUnit.Base\bin\Release\SSISUnitBase.dll
<extract folder> is the folder where you extracted the downloaded zip file.
<extract folder> is the folder where you extracted the downloaded zip file.
Mixed mode assemblies
The SSISUnit framework uses several COM components which were developed with older versions (.Net 2.0 and earlier) of the .Net Framework. Prior to .Net Framework 4, these components would not work as mixed mode assemblies and needed to be recompiled with same version of .Net as the hosting application. However, starting with .Net Framework 4, it is possible to leave them as they are to work in mixed mode provided a setting is added in the Visual Studio testing framework configuration file as follows.
Edit the file named:
vtest.executionengine.x86.exe.config
This file is usually located in the following folder:
<local drive>\Program Files (x86)\Microsoft Visual Studio 11.0\Common7\IDE\CommonExtensions\Microsoft\TestWindow\
vtest.executionengine.x86.exe.config
This file is usually located in the following folder:
<local drive>\Program Files (x86)\Microsoft Visual Studio 11.0\Common7\IDE\CommonExtensions\Microsoft\TestWindow\
Add the following setting:
<startup useLegacyV2RuntimeActivationPolicy="true"> </startup>
SSISUnit XML schema definition
With SSISUnit, we can create test definitions purely in XML. Although not strictly required, having a schema definition file (.xsd) does help a great deal in improving productivity and omitting errors through its intelli-sense and validation features. To enable this in Visual Studios, you can copy the SSISUnit.xsd file located in the <extract folder>\MAIN\SSISUnit\ folder to the Visual Studios XML schema definitions folder located in <local drive>\Program Files (x86)\Microsoft Visual Studio 11.0\xml\Schemas\. Once this file is copied to this location, Visual Studios should automatically detect the schema.
Thats all the setup that is required. Now will walk-through building a simple SSIS project and then add a unit test project using the Visual Studio testing framework.
Application scenario
Before proceeding further, a brief explanation of the sample application scenario maybe worth while. So this is a simple ETL process which maybe called by a master package, however for the sake of simplicity we will work with a single package. The scenario here is the loading of contacts data from a regular text file which is tab delimited and has the following column format.
Number :- A unique number for each contact of numeric type
Name :- The full name of the contact
Telephone :- A comma separated list of telephone numbers
Address :- The address of the contact
AddressDate :- The date when the contact was registered at the above address in yyyy-mm-dd format
The file will be named contacts.txt and delivered (perhaps by a master package) to a specific folder prior to executing the load package. Once successfully loaded this file could be removed, moved or renamed. For all intents and purposes, we are only interested in testing the SSIS package for loading the contacts from the source file to a destination database and not the file delivery and moving mechanism.
The file may contain duplicate records for the same contact in which case the load process should de-duplicate them based on the latest date value in the AddressDate column. Where their are duplicates with identical values in all columns, it should take a single record with distinct values. The processed records should then be checked against the database using the contact number business key that is held in the Number column. The contact numbers that do not exist in the database are inserted as new records. Where they already exist, those records need to be updated as type 1 changes (the existing values are overwritten). The Telephone column may contain blank values and needs to be accommodated in the database.
We will limit our ETL process to these few simple business rules, even through we know in reality they can become much more complex. These simple rules will allow us to more room to focus on understanding the SSISUnit Framework and how to integrate it with the Visual Studio unit testing framework.
The file may contain duplicate records for the same contact in which case the load process should de-duplicate them based on the latest date value in the AddressDate column. Where their are duplicates with identical values in all columns, it should take a single record with distinct values. The processed records should then be checked against the database using the contact number business key that is held in the Number column. The contact numbers that do not exist in the database are inserted as new records. Where they already exist, those records need to be updated as type 1 changes (the existing values are overwritten). The Telephone column may contain blank values and needs to be accommodated in the database.
We will limit our ETL process to these few simple business rules, even through we know in reality they can become much more complex. These simple rules will allow us to more room to focus on understanding the SSISUnit Framework and how to integrate it with the Visual Studio unit testing framework.
SQL database
Now its time to create the SQL Server database and the tables within it. For this purpose, you can either create a new database or use an existing database.
Run the following set of scripts using SQL Server Management Studio or Visual Studio SQL Server Object Explorer.
Once the database and the set of tables are created, we can proceed to develop the SSIS package.
To the solution add a new SSIS project and name the project SSISPackages or any other appropriate name. Rename the default package to LoadContacts.dtsx.
To the same solution add a new project, expand Visual C# folder and select Test. Then from the listed templates select Unit Test Project. Name the project SSISUnitTests. This will add a new Visual Studio unit test project.
To the SSISUnitTests project add a new C# class file named UnitTest_LoadContacts.cs. This is the file where we will write the code to drive the unit tests. We will also need to add a new .xml file to this project where we define the unit tests. Name this file as UnitTest_LoadContacts.ssisUnit.
At this point, in order to use the SSISUnit API, we need to reference the .dll files we built earlier. Inside the SSISUnitTest project right click on the References and select Add References option. Then browse to the location where the .dll files were built and add the 2 following files:
The following is is a snap shot of the solution explorer with the required projects and files added.
Visual Studio solution
In Visual Studio create a new blank solution. I always prefer to start with a blank solution and add the required projects. This is only my preference but is not a strict requirement. I have named my solution SampleSSISUnit.To the solution add a new SSIS project and name the project SSISPackages or any other appropriate name. Rename the default package to LoadContacts.dtsx.
To the same solution add a new project, expand Visual C# folder and select Test. Then from the listed templates select Unit Test Project. Name the project SSISUnitTests. This will add a new Visual Studio unit test project.
To the SSISUnitTests project add a new C# class file named UnitTest_LoadContacts.cs. This is the file where we will write the code to drive the unit tests. We will also need to add a new .xml file to this project where we define the unit tests. Name this file as UnitTest_LoadContacts.ssisUnit.
At this point, in order to use the SSISUnit API, we need to reference the .dll files we built earlier. Inside the SSISUnitTest project right click on the References and select Add References option. Then browse to the location where the .dll files were built and add the 2 following files:
<extract folder>\MAIN\SSISUnit\bin\Release\SSISUnit2012.dll
<extract folder>\MAIN\SSISUnit.Base\bin\Release\SSISUnitBase.dll
In addition to the above .dll files, we also need to add a reference to the Microsoft.SQLServer.ManagedDTS library version 11.0.0.0 (for SQL Server 2012). This library can be found in the Assemblies, Extensions. This is the library that allows the SSISunit Framework to execute SSIS packages from within the SSISUnitTest project.
In addition to the above .dll files, we also need to add a reference to the Microsoft.SQLServer.ManagedDTS library version 11.0.0.0 (for SQL Server 2012). This library can be found in the Assemblies, Extensions. This is the library that allows the SSISunit Framework to execute SSIS packages from within the SSISUnitTest project.
The following is is a snap shot of the solution explorer with the required projects and files added.
SSIS package
So now we can build this minimal SSIS package, to first stage and then load the contacts.txt file into the SQL database.
Open the LoadContacts.dtsx package in Visual Studio and add the 2 following package level variables:
pkDBConnectionString :- This string type variable will hold the OLEDB connection string to the detination SQL database.
pkSourceFilePath :- This string type variable will hold the file system path to the location where the contacts.txt file will be delivered.
Although not strictly required, holding this information in user variable, allow a grater degree of flexibility, enabling us when required to inject different sets of source data from different locations and to change the destination database to different servers via the test definition .xml file without having to change any code as you will see later on.
Now we need to add the following 2 connection managers:
cnFlatFile_Contacts :- This is a Flat File connection to the source data file. It uses an expression that concatenates the pkSourceFilePath variable with the string "contacts.txt" to form the fully qualified file name as follows. @[User::pkSourceFilePath] + "contacts.txt"
cnOLEDB_ContactsDB :- This is a OLEDB connection to the destination SQL database. It uses the pkDBConnectionString variable through an expression to construct the connection string.
We first clear the staging.Contacts table prior to staging the contacts source data. Add a Execute SQL task into the control flow. Set the connection of this task to the cnOLEDB_ContactsDB connection manager and code the following SQL directly into it.
Then add a Data Flow task consisting of a Flat File source component and an OLEDB destination component. Set the Flat File source component to cnFlatFile_Contacts connection manager and the OLEDB destination component to cnOLEDB_ContactsDB connection manager and select staging.Contact table from the drop down. Make sure the source to destination field level mappings are correct.
Now add another Data Flow task to load the data from staging to the final table. Construct this data flow task as follows:
We start with a OLEDB Source component that is set to the cnOLEDB_ContactsDB connection manager. Code the following SQL command into this component:
The above code fetches the staged data from the staging.Contacts table with an additional field named rno that indicates a row number based on the most recent AddressDate column for each contact number represented by the Number column. This field is used later on down the pipeline for the purposes of de-duplication.Now we need to add the following 2 connection managers:
cnFlatFile_Contacts :- This is a Flat File connection to the source data file. It uses an expression that concatenates the pkSourceFilePath variable with the string "contacts.txt" to form the fully qualified file name as follows. @[User::pkSourceFilePath] + "contacts.txt"
cnOLEDB_ContactsDB :- This is a OLEDB connection to the destination SQL database. It uses the pkDBConnectionString variable through an expression to construct the connection string.
We first clear the staging.Contacts table prior to staging the contacts source data. Add a Execute SQL task into the control flow. Set the connection of this task to the cnOLEDB_ContactsDB connection manager and code the following SQL directly into it.
Then add a Data Flow task consisting of a Flat File source component and an OLEDB destination component. Set the Flat File source component to cnFlatFile_Contacts connection manager and the OLEDB destination component to cnOLEDB_ContactsDB connection manager and select staging.Contact table from the drop down. Make sure the source to destination field level mappings are correct.
Now add another Data Flow task to load the data from staging to the final table. Construct this data flow task as follows:
We start with a OLEDB Source component that is set to the cnOLEDB_ContactsDB connection manager. Code the following SQL command into this component:
Next we add a Conditional Split component which uses a expression rno == 1 to check for de-duplicated records. All records with rno > 1 are considered duplicates and are ignored from the pipeline.
Add a Lookup component which will be used to check for the existence of contacts in the destination database. Set the component to use the cnOLEDB_ContactsDB connection manager and code the following SQL command to fetch the existing contact records from the database into the lookup.
Map the stg_Number from the source data to the id_Number field in the database table and select all other fields to be returned to the pipeline when a record is found.
Add another Conditional Split component and connect the 'Match' branch from the Lookup component to this split component. Here we will do a further check using an expression to see if any of the fields have changed between the staged source and destination records. Insert the following expression into the split condition:
Bellow the second split component add a OLEDB Command component and connect the split component to it. Set the cnOLEDB_ContactsDB connection manager to it and add the following SQL command.
The above command will update the matched record where a change has taken place. As can be seen the above command takes in the values for each of the fields as parameters. These parameters gets default names as param_0, param_1, param_2, param_3, param_4 in the order they appear in the command. Map each of these parameters to the staged source data fields as follows:
Please note that the above technique is not the most efficient way of updating records in a production environment, especially if the data set is large. However for the sake of simplicity we will use this method for this sample application.
For the 'No Match' branch of the lookup component, we will add a OLEDB Destination component and connect that branch to it. All new contact records with no matching numbers will travel via this branch to get inserted into the destination table. Set the cnOLEDB_ContactsDB connection manager to the destination component and select the destination table dim_Contacts from the drop down. Map all the fields from the sourced staging data set to the destination table as follows. Ignoring the key_Contact field since this is an incremental auto-identity field.
Unit test project
Now that the SSIS package has been built, we will see how to develop some basic unit tests that will cover the most important business rules of this ETL process.
As mentioned earlier we will be using the SSISUnit .Net API with C# via the Visual Studio's own built-in unit testing framework. The main benefit you get with this method over the others, is that, you can now perform the unit tests as part of the solution build process. This can also be easily incorporated into a continuous integration process that you may be carrying out on regular basis. All the code relating to the application and tests are all located together within the same solution and saves you the trouble of maintaining any additional testing scripts in other locations.
SSISUnit offers the ability perform unit tests at individual task level or entire package level. At which level you conduct your tests depend entirely on the complexity of the application. For our sample project, we will be conducting one test at the task level and others at the package level, so you get experience both approaches.
All tests will follow the traditional xUnit structure of Setup, Test and Tear-down.
Test 1 :- Staging contacts data
In this test we will test the individual data flow task within the package that loads the data from the source file to the staging.Contacts table. For this we will use the following data set in a tab delimited text file. We will save this file as contacts_batch1.txt inside a sub folder named SourceFiles within the unit test project folder. As we shall see later, the test setup will take a copy of this file into the contacts.txt file which is used by the package.
| Number | Name | Telephone | Address | AddressDate |
|---|---|---|---|---|
| 1 | James | 787,387,423,568,938,000,000,000,000,000,000 | 24 Harper Ave, Middlesex, MH2 4BU | 22/04/2003 |
| 1 | James | 787,387,423,568,938,000,000,000,000,000,000 | 154 Arvin Drive, Middlesex, MH5 7GD | 14/12/2005 |
| 2 | Charls | 7,656,928,671,776,520,000,000 | 1A Park Street, Bedford, BD7 9OP | 16/01/1987 |
| 3 | Carol | 30 Church Road, Brighton, Essex, BR8 7UT | 02/09/2000 | |
| 4 | Sarah | 9,897,876,521,276,760,000,000 | 38 Kings Lane, Wimbledon, London, SW7 5TR | 07/12/2006 |
| 4 | Sarah | 9,897,876,521,276,760,000,000 | 38 Kings Lane, Wimbledon, London, SW7 5TR | 07/12/2006 |
Now we need to create a test definition for this in xml. To do this open the UnitTest_LoadContacts.ssisUnit file that we added to the unit test project earlier and insert the following xml code.
If you take a closer look at the above xml, you will see some basic elements that are organized as follows.
<TestSuite>
<!-- A Test Suite is the main container for the entire test definition. A Test Suite contain one or more
individual unit tests -->
<ConnectionList>
<!-- A Connection List contain one or more connection string to specific databases. These
connections are then referenced from elsewhere in the definition to issue SQL commands to
those databases --></ConnectionList>
<PackageList>
<!-- A Package List contain one or more fully qualified package paths pointing to individual SSIS
packages. These are then referenced from elsewhere in the definition --> </PackageList>
<TestSuiteSetup>
<!-- Any setup that is required and common to all tests within the test suite are defined within this
element. Setup can contain multiple commands, which can perform different types of
operations. The supported command types are SQLCommand, ProcessCommand and
VariableCommand. Note that this setup only runs once for the entire test suite-->
</TestSuiteSetup>
<Setup>
<!-- Similar to the previous element, this element also may contain multiple commands. However
unlike the previous setup this setup runs once for each individual test defined within the test
suite. Hence these commands are intended to perform any common operations that are
needed prior to running individual tests. --></Setup>
<Tests>
<!-- This element is acts as the container that holds one or more individual tests to be defines for
the test suite. -->
<Test>
<!-- This is the container element for an individual test. Each test will follow the Setup, Test,
Tear-down structure. -->
<TestSetup>
<!-- Within this element, any setup needed for this specific setup will be defined. Similar to
the other setup's, the command types used here can be SQLCommand,
ProcessCommand or VariableCommand. --></TestSetup>
<Assert>
<!-- This is the container element for the assertion definition of this individual test. Here the
expected results and the type of this assertion is established. Inside this element there can
be one more more commands defined that checks for the actual results of the test. These
command are of type SQLCommand, ProcessCommand and VariableCommand. -->
</Assert>
<TestTearDown>
<!-- Within this element there can be multiple commands that perform any operations needed
to clean up or reinstate data after the test has completed --></TestTearDown>
</Test>
</Tests>
<TearDown>
<!-- This element may contain one or more commands that perform any common operations
needed to clean up or reinstate data after each individual test within the test suite has
completed. --></TearDown>
<TestSuiteTearDown>
<!-- This element may contain one or more command that perform any operations needed to clean
up or reinstate data after the entire test suite has completed. Unlike the previous tear down
element, the command contained within this element will only run once for the entire test
suite. --></TestSuiteTearDown>
</TestSuite>
Given the above information, lets take some time to examine the xml code for the test definition. We start by looking at the <Test> element which declares the unit test. We give its name attribute to be "TestBatch1Staging". The package attribute carries a reference to the SSIS package listed in the <PackageList> element at the top of the definition file. The task attribute specifies which specific task within the to execute to conduct the test. In this case we are testing the staging Data Flow named as cfDFT_StageContacts (this is a naming convention that I follow). In case we were testing the entire package, then the task attribute will carry the name of the package itself. The taskResult attribute indicates the expected result of the task or package and not the expected result of the unit test itself.
Next we look at the <TestSetup> element. This is where the commands that perform the test setup is specified. For this test, we have used three types of commands.
First we have a VariableCommand, which can perform Set and Get operations on SSIS package variables. This is a very useful feature which allows you to inject different values into the package for different test conditions or environments. Here we use it to set the file path to the contacts.txt source data file in the pkSourceFilePath variable. We also set the connection string for the destination database in the pkDBConnectionString variable.
Then we use a FileCommand, which can perform basic system file operations. We use this here to copy our Batch1 data set stored in the file contacts_batch1.txt to the contacts.txt file. This method gives us the flexibility to inject different data sets for different tests
Another SQLCommand is used to clear the staging table before running the test. This is performed by issuing the TRUNCATE TABLE staging.Contacts command on the database referenced by the connectionRef attribute.
Please note that all the folder paths, database names and connection strings specified in the code are referring to my own development environment. So you will need to replace them with the appropriate values for your environment.
Now look at the <Assert> element. Here we define its name as "AssertStagingContacts". The expectedResult attribute is "6" which is the number of records in the contacts_batch1.txt file and what we also expect to find in the staging table after the staging data flow has run. testBefore attribute is to define whether the assertion should run before the task has run. In this case we want it to run after the task has run, so we set it to false. The expression attribute is a boolean to indicate whether the assertion is an expression which in this case is not, so we set it to false.
Now we set the assertion command. You can use a number of different types of commands. For this test we use a SQLCommand named as "CmdCountStagingContacts" its connectionRef is set to "ContactsDB" which refers to the connection listed under the <ConnectionList> element at the very top of the test suite definition. The returnsValue attribute is set to "true" to indicates that the SQLCommand issued here is a simple SELECT COUNT(*) FROM staging.Contacts which returns a scalar value that will be compared to the value set in the expectedResult attribute to determine the passing or failure of the test. So if the SQL command returns a record count of 6, the test will pass, otherwise it will fail.
That concludes the definition of the test suite and the individual test within it.
Next, we need write the unit test code in C# using the SSISUnit .Net API to execute the test. This is done in the UnitTests_LoadContacts.cs file we added earlier to the project. So lets examine the code to execute the above test.
As can be seen above, the SSISUnit .Net API has been referenced by using the SsisUnit and SsisUnitBase namespaces. In addition a reference to System.xml namespace is also added to allow SSISUnit to serialize and de-serialize xml.
When you create a Unit Test Project in Visual Studios, it adds a class with the attribute [TestClass] with a public Initialize method with the attribute [TestInitialize]. The Initialize method is intended to be used for the purposes of setting configurations and allocating resources needed by all the tests in the class. In SSISUnit this initialization work will be carried out by the API based on the definitions specified within the Setup elements and so we would not need to directly code anything in the Initialize method.
Within the test class, we will create the following private member variables which will be used by all the tests.
private SsisTestSuite testSuite; /* This will hold a reference to the test suite object used by all tests */
private TestResult testResult; /* This will hold a reference to the test result object used by all tests */
private Test test; /* This will hold a reference to an individual test object */
private Context context; /* This holds a reference to a context object that is used by the test */
private bool isTestPassed; /* This is a boolean that indicates if the test has passed */
SSISUnit delegates several events that gets fired at various stages in the test execution cycle. The one that we are interested in for this project is the AssertCompleted event. This gets fired when SSISUnit has completed an Assert operation of a test. We will subscribe the following public method as the event handler to this event to handle the assertions for all the tests.
public void TestSuiteAssertCompleted(object sender, AssertCompletedEventArgs e)
{
if (e.AssertName != null)
{
testResult = e.TestExecResult;
isTestPassed = e.TestExecResult.TestPassed;
}
}
The e parameter is an object that contains all the information pertaining to the assertion. In the most simplistic use of this method we simply check the e.TestExecResult.TestPassed property indicates whether the test passed or not and assign its boolean value to the isTestPassed member variable.
We now need to add a method to conduct the test. This should be a public method with the attribute [TestMethod] added at the top. Its best to give this method the same name that was used for its definition in the xml file which in this case is TestBatch1Staging as shown bellow.
[TestMethod]
public void TestBatch1Staging()
{
testSuite = new SsisTestSuite(@"<path to the xml file>\UnitTest_LoadContacts.ssisUnit");
test = testSuite.Tests["TestBatch1Staging"];
testSuite.AssertCompleted += TestSuiteAssertCompleted;
isTestPassed = false;
context = testSuite.CreateContext();
bool rs = test.Execute(context);
Assert.AreEqual<bool>(true, isTestPassed);
testSuite.AssertCompleted -= TestSuiteAssertCompleted;
}
The first line creates a new SsisTestSuite object by passing in the test definition xml file as a parameter. The resulting object will contain all the information about the test suite.
Next we access the specific test from the Tests collection property by specifying the test name as an index.
Then we subscribe our TestSuiteAssertCompleted method to the AssertCompleted event. The isTestPassed member variable is set to false before running the test. This is so that the default result of the test will always be false until the test result is assigned to it.
A new SSISUnit test context object is created which contains any references to system file paths and database connections.
Then we execute the test by calling test.Execute method passing to it the context as a parameter. This is the method that actually runs the SSIS package or task that was specified in the test definition. The method returns a boolean that indicates whether the package or task ran successfully. Note that this is not an indication about the pass or failure of the unit test itself. That happens next with the assertion.
Now we do the assertion with the Assert.AreEqual method which is a method of the Visual Studio unit test framework. This method takes in 2 boolean values as parameters, the first being the expected value and the second being the actual value which in this case is the value held in the isTestPassed member variable. If the two values match then the test is considered passed otherwise its a fail.
Finally we unsubscribe our assertion event handler so that another test can subscribe to it.
Test 2 :- Updating contacts with de-duplication
In this unit test we want to test the entire package execution to see if it correctly updates existing data by first de-duplicating any duplicate contact records that may exist in the source data file. In order to achieve this we will use a second batch of data in the file contacts_batch2.txt as given bellow:
| Number | Name | Telephone | Address | AddressDate |
|---|---|---|---|---|
| 5 | Paul | 676,256,354,316,735,000,000,000,000,000,000 | 201A Jasper Street, Croydon, London, CR4 9KT | 13/06/2003 |
| 2 | Charls | 765,692,867,177,652,000,000,000,000,000,000 | 57 Tinpan Alley, Bedford, BD4 3GH | 10/10/2000 |
| 2 | Charls | 765,692,867,177,652,000,000,000,000,000,000 | 78 Station Road, Bedford, BD3 6TU | 15/04/2001 |
Now lets define this test by coding the following xml into the UnitTest_LoacContacts.ssisUnit file.
Note that the above code should be inserted between the <Tests> and </Tests> elements. The test has been given a name as "TestBatch2OldDedupContact". This is the naming convention I follow, however you can choose any meaning name and also include spaces to make it more readable. You will notice that unlike the previous test Test 1, here the package and task attributes carries the same value which is the name of the package. This is because for this test the unit we are testing is the whole package. The <TestSetup> element is similar to Test 1 except that now the sourcePath attribute of the FileCommand elements refers to the contacts_batch2.txt file which will be copied overwriting the existing contacts.txt file if it already exist. Also notice that the final SQLCommand which truncates the staging table has been removed. The reason behind this would become more apparent when we examine the .Net C# code that will drives this test.
Look at the the <Assert> element. We have set its name attribute to be "AssertOldDedupContact". Again you may chose a more meaningful name if you wish, as this naming convention is just the one I have been using. The expectedResult is set to "1". The reason for this expected value will become apparent when we see the assertion SQL command. The other attributes for this element remain the same as the previous test.
For the assertion command we again use a SQLCommand with a the name attribute set as "CmdGetContactValues", and the rest of the attributes set exactly the same as the previous test. The SQL command that is issued to assert the test is as follows:
SELECT
CASE WHEN
Name = 'Charls' AND
Telephone = '76569286717,76520989012,87293752635' AND
Address = '78 Station Road, Bedford, BD3 6TU' AND
AddressDate = '2001-04-15'
THEN 1 ELSE 0 END
FROM dim_Contacts
WHERE id_Number=2
This command simply filters contact number 2 and checks for specific field values that we know are the correct values for the test to pass. If they all match then the CASE statement will return a 1, otherwise a 0. When the return value is compared to the value set in the expectedResult attribute and if they are both 1, then the test will pass, otherwise it will fail.
SELECT
CASE WHEN
Name = 'Charls' AND
Telephone = '76569286717,76520989012,87293752635' AND
Address = '78 Station Road, Bedford, BD3 6TU' AND
AddressDate = '2001-04-15'
THEN 1 ELSE 0 END
FROM dim_Contacts
WHERE id_Number=2
This command simply filters contact number 2 and checks for specific field values that we know are the correct values for the test to pass. If they all match then the CASE statement will return a 1, otherwise a 0. When the return value is compared to the value set in the expectedResult attribute and if they are both 1, then the test will pass, otherwise it will fail.
Now lets look at the .Net code in C# in the unit test project and see how this test is conducted via the SSISUnit .Net API. To do this we add a new method to the UnitTest_LoadContacts.cs class file in Visual Studio as follows:
As in the previous test we give the method same as given to the unit test definition. As the code for this test is very similar to the previous test, you can refer to the explanation given earlier for an understanding of the API. Where the code for this test differs from the previous one is that here we are executing two tests. The first one "TestBatch1NewContacts" is executed first, but no assertion is done. This is a separate test definition created to test for new records from the contacts_batch1.txt file being correctly inserted. Here we are hijacking that test in order to initialize our destination dim_Contacts table with data that we can then update via the second test and perform the required test assertion.
So as you can see in the above code only one Assert.AreEqual method is called to assert the second test.
So as you can see in the above code only one Assert.AreEqual method is called to assert the second test.
Other tests
Apart from the two tests mentioned above, I have created a few more tests that tests several application scenarios all of which are too much to document in this article. However you can download the entire sample with all the tests from my GitHub repository.
Running the test in Visual Studio Test Explorer
Before being able to run the tests, the unit test project needs to be built. When built all the methods with the attribute [TestMethod] attached to them will appear in the Test Explorer. You will then have a number of options on how to run them. You can also set all the tests to be run automatically when ever the solution is built which maybe useful for Continuous Integration builds. For more information about how to use the Visual Studio Test Explorer please refer to this MSDN link.To view the Test Explorer, in Visual Studio simple select the Test menu, then Windows, the Test Explorer. You should see a window similar to the one bellow:
To run an individual test simply right click on a test and select 'Run Selected Test'. The test will execute and if passed will be indicated with a greed tick. If failed it will be indicated with a red exclamation mark.