• Article

August 2013

Volume 28 Number 8

SQL Server - Unit and Integration Testing of SSIS Packages

By Pavle Guduric

I worked on a project where we built extract, transform and load (ETL) processes with more than 150 packages. Many of them contained complex transformations and business logic, thus were not simple “move data from point A to point B” packages. Making minor changes was not straightforward and results were often unpredictable. To test packages, we used to fill input tables or files with test data, execute the package or task in Microsoft Business Intelligence Development Studio (BIDS), write a SQL query and compare the output produced by the package with what we thought was the correct output. More often we just ran the whole ETL process on a sample database and just sampled the output data at the end of the process—a time-consuming and unreliable procedure. Unfortunately, this is a common practice among SQL Server Integration Services (SSIS) developers. Even more challenging is to determine what effects the execution of one package has on subsequent packages. As you build your ETL process, you create a network of connected packages and different resources. It’s difficult to maintain a complete overview of the numerous dependencies among all of these at all times.

This article explains how to perform unit and integration testing of SSIS packages by introducing a library called SSISTester, which is built on top of the managed SSIS API. After reading this article you should be able to use the described techniques and tools to automate unit and integration testing of your existing and new SSIS projects. To understand the article, you should have previous experience with SSIS and C#.

SSISTester

When I started thinking about a testing framework for SSIS packages, I found three aspects to be important. First, I wanted to have a similar UX to writing tests using the Visual Studio testing framework, so the typical methodology involving setup, verification and cleanup (aka teardown) steps had to be applied. Second, I wanted to use existing and proven tools to write, execute and manage tests. Once again, Visual Studio was the obvious choice. And third, I wanted to be able to code tests in C#. With that in mind I wrote SSISTester, a .NET library that sits on top of the SSIS runtime and exposes an API that allows you to write and execute tests for SSIS packages. The main logical components of the library are depicted in Figure1.

Logical Components of the SSISTester Library
Figure 1 Logical Components of the SSISTester Library

The Package Repository is used to store raw XML representations of target packages. Each time a test is executed, a new instance of the Microsoft.SqlServer.Dts.Runtime.Package class is deserialized from XML with all fields and properties set to their default values. This is important because you don’t want different tests that target the same package to accidently reuse any of the values set by previous tests.

Instances of test classes are stored within the Test Repository. These classes contain methods that implement your test cases. When a test is executed, these methods are called by the Test Engine. The specific rules that must be followed when creating test classes will be described in detail later.

Metadata contains the attributes needed to decorate a test class so it can be recognized as a test implementation. The Test Engine looks for these attributes when loading tests into the Test Repository.

The Test Context represents a set of classes that provide access to the runtime information during different phases of the test execution. For example, you can use these classes to access different aspects of a package being tested, such as variables, properties, preceding constraints, connection managers, currently executing task, package errors and so forth.

The Test Engine refers to the core classes and interfaces of the SSISTester API that directly utilize the managed SSIS runtime. They are used to load packages and test classes into their respective repositories, as well as to execute tests and to create test results.

Mini ETL

To create packages and test classes, I’ll use Visual Studio 2012 and SQL Server 2012, and I’ll use three packages to illustrate a simple ETL scenario in which customer data, delivered as a text file, is transformed and stored within a database. The packages are CopyCustomers.dtsx, LoadCustomers.dtsx and Main.dtsx. CopyCustomers.dtsx copies the Customers.txt file from one location to another and on the way it converts all customer names to uppercase text. Customers.txt is a simple CSV file that contains ids and names of customers, like so:

id,name
1,company1
5,company2
11,company3

LoadCustomers.dtsx loads the converted names into the Demo database. Before it loads data into a target table called Customers­Staging, it truncates all previously stored data. At the end of the process, it stores the number of customers into a variable. Here’s the script to create the Demo database and the CustomersStaging table:

CREATE
DATABASE [Demo]
GO
USE [Demo]
GO
CREATE TABLE [dbo].[CustomersStaging](
  [Id] [int] NULL,
  [Name] [nvarchar](255) NULL
) ON [PRIMARY]
GO

The package Main.dtsx contains two Execute Package tasks that execute the sub-packages CopyCustomers.dtsx and Load­Customers.dtsx, respectively. Connection managers in both CopyCustomers.dtsx and LoadCustomers.dtsx are configured using expressions and package variables. The same package variables are retrieved from the parent package configuration when executed from within another package.

Creating Unit Tests

To begin, create a console project and add assembly references to SSIS.Test.dll and SSIS.Test.Report.dll. I’m going to create a unit test for the CopyCustomers.dtsx package first. Figure 2 shows the control flow (left) and data flow (right) for CopyCustomers.dtsx.

Control Flow (Left) and Data Flow (Right) of the CopyCustomers.dtsx Package
Figure 2 Control Flow (Left) and Data Flow (Right) of the CopyCustomers.dtsx Package

Every unit test is implemented in a single class that derives from the BaseUnitTest class and must be decorated with the UnitTest attribute:

[UnitTest("CUSTOMERS", "CopyCustomers.dtsx")]
public class CopyCustomersTest : BaseUnitTest{
  protected override void Setup(SetupContext context){}
  protected override void Verify(VerificationContext context){}
  protected override void Teardown(TeardownContext context){}
}

The UnitTest attribute marks a class as a unit test implementation so it can be found by the Test Engine. The first parameter corresponds to the Package Repository where a target package will be loaded during test execution, CUSTOMERS in this example. The second parameter can be the name of a target package, the path to a task in the control flow, the path to an event handler or the path to a preceding constraint. In this example it’s the name of the CopyCustomers.dtsx package because I want to test the whole package. Basically, the UnitTest attribute tells the Test Engine to look for the CopyCustomers.dtsx package in the CUSTOMERS repository and execute it during the CopyCustomersTest test.

The base class BaseUnitTest that all unit test implementations need to derive from contains three methods that have to be implemented: Setup, Verify and Teardown.

These three methods are executed during different test phases. The Setup method runs before a target package is executed by the Test Engine. Setup prepares the package and all inputs and outputs the package depends on so it can be successfully validated and executed. In the following example, I set paths to the package variables that are used as connection strings in the connection managers:

protected override void Setup(SetupContext context){
  if(File.Exists(@"C:\TestFiles\Archive\Customers.txt"))
    File.Delete(@"C:\TestFiles\Archive\Customers.txt");
  if(File.Exists(@"C:\TestFiles\Converted\Customers.txt"))
    File.Delete(@"C:\TestFiles\Converted\Customers.txt");
  DtsVariable sourceFile = context.Package.GetVariable("SourcePath");
  sourceFile.SetValue(@"\\nc1\Customers\Customers.txt");
  DtsVariable destinationFile = 
    context.Package.GetVariable("DestinationPath");
  destinationFile.SetValue(@"C:\TestFiles\Archive\Customers.txt");
  DtsVariable convertedFile = 
    context.Package.GetVariable("ConvertDestinationPath");
  convertedFile.SetValue(@"C:\TestFiles\Converted\Customers.txt");
}

After the Setup method has successfully executed, Test Engine executes the target package. When the package has executed, Test Engine calls the Verify method and I can check whether my assertions are true:

protected override void Verify(VerificationContext context){
  Assert.AreEqual(true, 
    context.Package.IsExecutionSuccess);
  Assert.AreEqual(true, 
    File.Exists(@"C:\TestFiles\Archive\Customers.txt"));
  Assert.AreEqual(true, 
    File.Exists(@"C:\TestFiles\Converted\Customers.txt"));
  string[] lines = 
    File.ReadAllLines(@"C:\TestFiles\Converted\Customers.txt");
  Assert.AreEqual("COMPANY2", lines[2].Split(',')[1]);
}

The first assert checks whether the package has executed successfully. The second one determines whether the FST Copy Source File file system task copied the \\nc1\Customers\Customers.txt file to the C:\TestFiles\Archive\ folder. The last two asserts validate whether the DFT Convert Customer Names data flow task correctly converted company names to uppercase. Earlier, I briefly described the testing context. Here you can see how I used the context parameter to access a package object within the Setup and Verify methods.

At the end of the test, I use the Teardown method to delete the files that were copied or created by the package:

protected override void Teardown(TeardownContext context){
  File.Delete(@"C:\TestFiles\Archive\Customers.txt");
  File.Delete(@"C:\TestFiles\Converted\Customers.txt");
}

Testing Control Flow Tasks

Tests can target specific tasks in the control flow as well. For example, to test the DFT Load Customers data flow in the LoadCustomers.dtsx package, I used an additional parameter of the UnitTest attribute, called ExecutableName, to tell the Test Engine that I want to test this task:

[UnitTest("CUSTOMERS", "LoadCustomers.dtsx",ExecutableName =
  @"\[LoadCustomers]\[SEQC Load]\[DFT Load customers]"))]
public class LoadCustomersTest : BaseUnitTest{
}

ExecutableName represents the path that combines names of all nested containers beginning with a package name.

Control and data flow for LoadCustomers.dtsx are shown in Figure3.

Control Flow (Left) and Data Flow (Right) of the LoadCustomers.dtsx Package
Figure 3 Control Flow (Left) and Data Flow (Right) of the LoadCustomers.dtsx Package

When a test targets a specific task, only that task is executed by the Test Engine. If the successful execution of the target task depends on the execution of preceding tasks, the results of executing those tasks need to be manually generated. The DFT Load Customers data flow expects the target table to be truncated by the SQL Truncate CustomersStaging task. Further, the data flow expects the transformed Customers.txt file at a specific location. Because this file is created by the CopyCustomers.dtsx package, I need to copy it manually. Here’s the Setup method that does all this:

protected override void Setup(SetupContext context){
  string dbConStr = @"Data Source=.;Integrated Security=SSPI;Initial Catalog=Demo";
  string ssisConStr = @"Provider=SQLNCLI11;" + dbConStr;
  File.Copy(@"\\nc1\Customers\Customers.txt", 
    @"C:\TestFiles\Converted\Customers.txt");
  context.DataAccess.OpenConnection(dbConStr);
  context.DataAccess.ExecuteNonQuery("truncate table [dbo].[CustomersStaging]");
  context.DataAccess.CloseConnection();
  DtsConnection conn = context.Package.GetConnection("CustomerDB");
  conn.SetConnectionString(ssisConnStr);
  conn = context.Package.GetConnection("CustomersSrc");
  conn.SetConnectionString(@"C:\TestFiles\Converted\Customers.txt");
}

By using File.Copy, I copy the Customers.txt to the location expected by the data flow. Then I use the DataAccess property of the SetupContext to execute a truncate statement on the target table. This property exposes a lightweight ADO.NET wrapper that enables you to execute SQL commands without having to use SqlConnection and SqlCommand classes every time you want to access the database. At the end, I use the Package property to set the connection strings to the underlying connection managers.

Testing Preceding Constraints

Writing tests that target preceding constraints is also possible. For example, the CountConstraint that precedes the SCR CheckCount script task in the LoadCustomers.dtsx package has an expression that checks whether the variable CustomerCount is greater than zero. If this expression evaluates to true and the SEQC Load task executes successfully, then the script task is executed. Figure 4 shows the complete unit test.

Figure 4 The Complete Unit Test

[UnitTest("CUSTOMERS", "LoadCustomers.dtsx",
    PrecedenceConstraintsTestOnly = true))]
public class LoadCustomersConstraintsTest : BaseUnitTest{
  private DtsPrecedenceConstraint _countConstraint;
  protected override void Setup(SetupContext context){
    DtsVariable variable = context.Package.GetVariable("CustomerCount");
    variable.SetValue(0);
    _countConstraint =
      context.Package.GetPrecedingConstraintForPath(
      @"\[LoadCustomers]\[SCR    CheckCount].[CountConstraint]");
    _countConstraint.SetExecutionResult(DtsExecutionResult.Success);
  }
  protected override void Verify(VerificationContext context)
  {
    Assert.AreEqual(false, _countConstraint.Evaluate());
  }
  protected override void Teardown(TeardownContext context){}
}

To prepare the precedence constraint to be tested, I need to do two things. First, I have to set the CustomerCount variable to some value, because the expression in the precedence constraint refers to it. In this case, I choose 0. Next, I set the execution result of the preceding task to success, failure or completion. I do this by using the SetExecutionResult method to simulate success of the preceding task. This means that CountConstraint should evaluate to false and this is what I expect in the Verify method. You can have only one class where you implement unit tests for all preceding constraints in a package. Therefore, there’s no target path to the particular constraint in the UnitTest attribute, only a Bool flag that tells the engine that this is a unit test class for precedence constraints. The reason for this is that with precedence constraints, there’s no need to execute the package or task before the Verify method is called.

Executing Unit Tests

Before I can execute my tests, I need to load target packages and tests into their repositories. To do this, I need a reference to the Test Engine. Open the Program.cs file and replace the empty Main method with this one:

static void Main{
  IUnitTestEngine engine = 
    EngineFactory.GetClassInstance<IUnitTestEngine>();
  engine.LoadPackages("CUSTOMERS", @"C:\TargetPackages\");
  engine.LoadUnitTests();
  engine.ExecuteUnitTestsWithGui();
}

The first line creates a reference to the Test Engine. To load all packages from the folder C:\TargetPackages\ into the CUSTOMERS repository, I use the LoadPackages method. The LoadUnitTests method loads all classes in the calling assembly that are decorated with the UnitTest attribute into the specified test repository. Finally, I call ExecuteUnitTestsWithGui to start the execution of tests and to open the monitoring GUI, which is shown in Figure5.

The Monitoring GUI During the Execution of Tests
Figure 5 The Monitoring GUI During the Execution of Tests

The GUI in Figure5 is practical if you want to test locally on your machine and you don’t want to start Visual Studio. If you’d like to test packages on a server, you could make small modifications to the program and schedule it to run tests directly on a build server, for example:

static void Main{
  IUnitTestEngine engine = 
    EngineFactory.GetClassInstance<IUnitTestEngine>();
  engine.LoadPackages("CUSTOMERS", @"C:\TargetPackages\");
  engine.LoadUnitTests();
  engine.ExecuteUnitTests();
  engine.UnitTestResults.SaveAsHtml(@"C:\TestResults\");
}

The IUnitTestEngine interface has the UnitTestResults property that lets you access test results and save them as an HTML report. I replaced ExecuteUnitTestsWithGui with ExecuteUnitTests, which doesn’t show the monitoring GUI. You could also run tests inside Visual Studio or use ReSharper so you don’t need to start the console program. To do this, I created the new class called SSISUnitTestAdapter, shown in Figure 6.

Figure 6 The SSISUnitTestAdapter Class

[TestClass]
public class SSISUnitTestAdapter{
  IUnitTestEngine Engine {get;set;}
  [AssemblyInitialize]
  public static void Prepare(TestContext context){
    Engine = EngineFactory.GetClassInstance<IUnitTestEngine>();
    Engine.LoadPackages("CUSTOMERS", @"C:\TargetPackages\");
    Assembly testAssembly =
      Assembly.GetAssembly(typeof(CopyCustomersTest));
    Engine.LoadRepositoryUnitTests(testAssembly, "CUSTOMERS");
  }
  [TestMethod]
  public void CopyCustomersTest(){
    Engine.ExecuteUnitTest(typeof(CopyCustomersTest));
  }
  [TestMethod]
  public void LoadCustomersTest(){
    Engine.ExecuteUnitTest(typeof(LoadCustomersTest));
  }
  [TestMethod]
  public void LoadCustomersConstraintsTest(){
    Engine.ExecuteUnitTest(typeof(LoadCustomersConstraintsTest));
  }
}

If you’ve worked with the Microsoft unit testing framework before, you’ll recognize the TestClass, AssemblyInitialize and TestMethods attributes. The three test methods, CopyCustomersTest, LoadCustomersTest and LoadCustomersConstraintsTest, wrap the call of the ExecuteUnitTest method, which in turn executes the Setup, Verify and Teardown methods of the class that’s passed as parameter. The Prepare method creates the Test Engine object and loads packages and unit tests into their respective repositories. I used slightly different methods called LoadRepositoryUnitTests to load tests bound to the CUSTOMERS repository only. This is useful if you don’t want to load all tests. You can execute all tests by clicking on Tests | Execute | All Tests in Visual Studio.

Creating Integration Tests

The basic idea of unit tests is to isolate all of the possible effects other packages or tasks may have on the one being tested. Sometimes it can be challenging to create a realistic test setup and the initial conditions needed for a unit test to ensure the package or task being tested behaves like a part of a complete ETL process. Because you usually implement ETL processes with a number of packages, you need to perform integration tests to be sure that each package works well when run as part of that process. The idea is to define probing points in your ETL process where you want to perform tests, without having to stop the whole process. As the process progresses and reaches the probing point, your tests are executed and you can verify a “live” work-in-progress ETL process; hence the name, “live test.”

A live test is basically a post-condition—defined for a package, task or event handler—that needs to be satisfied after the package, task or event handler has executed. This post-condition corresponds to the verification step of a unit test. Live tests are different from the unit tests because it’s not possible to prepare the test prior to package execution or to perform a clean-up step afterward. This is because unlike a unit test, a live test doesn’t execute the package; it’s the other way round: A package executes a test when it comes to the probing point for which a post-condition is defined.

Figure 7 illustrates this difference. Note the position of the package in both figures. When running unit tests, the Test Engine explicitly executes a unit test by calling its Setup, Verify and Teardown methods. A package is executed as a part of this Setup-Verify-Teardown sequence.

Sequence Diagrams for Unit Test (Left) and Live Test (Right) Execution
Figure 7 Sequence Diagrams for Unit Test (Left) and Live Test (Right) Execution

On the other hand, when running live tests, the Test Engine executes a package explicitly, which in turn triggers the execution of action methods that implement the post-conditions for a package and its tasks.

In order to create a live test for the CopyCustomers.dtsx package, I created the new class called CopyCustomers, shown in Figure 8.

Figure 8 The CopyCustomers Class

[ActionClass("CUSTOMERS", "CopyCustomers.dtsx")]
public class CopyCustomers : BaseLiveTest{ 
  [ActionMethod(@"\[CopyCustomers]")]
  public void TestWholePackage(ActionContext context){
    Assert.AreEqual(true, context.Package.IsExecutionSuccess);
  }
  [ActionMethod(@"\[CopyCustomers]\[FST Copy Source File]")]
  public void TestCopySourceFile(ActionContext context){
    Assert.AreEqual(true, 
        context.ActiveExecutable.IsExecutionSuccess);
    Assert.AreEqual(true, 
        File.Exists(@"C:\TestFiles\Archive\Customers.txt"));
  }
  [ActionMethod(@"\[CopyCustomers]\[DFT Convert customer names]")]
  public void TestConvertCustomersNames(ActionContext context){
    Assert.AreEqual(true, context.ActiveExecutable.IsExecutionSuccess);
    string[] lines = 
        File.ReadAllLines(@"C:\TestFiles\Converted\Customers.txt");
    Assert.AreEqual("COMPANY2", lines[2].Split(‘,’)[1]);
  }
}

Each live test class must derive from the BaseLiveTest class, a major difference when compared with a unit test. The BaseLiveTest class is used internally by the Test Engine to execute live tests and has no methods that have to be overridden. The ActionClass attribute marks this class as a live test. The parameters are the same as when using the UnitTest attribute—repository and target package. Note that unlike unit tests where each test is implemented in a single, separate class, only one class is needed to implement all post-conditions for a package. Live test classes can have an arbitrary number of post-conditions that should be evaluated. These post-conditions correspond to the Verify method in a unit test and are implemented as methods decorated with the ActionMethod attribute. In the example in Figure 8, I have one post-condition for each task in the package and one for the package itself. ActionMethod accepts a path to the target task, which is the same as the ExecutableName in the UnitTest attribute. This tells the Test Engine to execute this method when the target task has executed. Unlike the Verify method, which is always executed, these post-conditions might not be called when, for example, the target task doesn’t execute successfully or the preceding constraint evaluates to false. The ActionContext parameter provides the same functionality as the VerificationContext.

Executing Live Tests

The steps necessary to execute live tests are slightly different than when executing unit tests. To execute live tests, replace the Main method in the Program.cs file with the code in Figure 9.

Figure 9 The Main Method for Executing Live Tests

static void Main{
  string dbConStr = @"Data Source=.;Integrated Security=SSPI;Initial Catalog=Demo";
  string ssisConStr = @"Provider=SQLNCLI11;" + dbConStr;
  ILiveTestEngine engine = 
    EngineFactory.GetClassInstance<ILiveTestEngine>();
  engine.LoadPackages("CUSTOMERS", @"C:\TargetPackages\");
  engine.LoadActions();
  ExecutionParameters params = new ExecutionParameters();
  params.AddVariable(@"\[Main].[ConnectionString]", ssisConStr);
  params.AddVariable(@"\[Main].[CopyCustomersPath]", 
    @"C:\TargetPackages\CopyCustomers.dtsx");
  params.AddVariable(@"\[Main].[LoadCustomersPath]", 
    @"C:\TargetPackages\LoadCustomers.dtsx");
  params.AddVariable(@"\[Main].[ConvertDestinationPath]",
    @"C:\TestFiles\Converted\Customers.txt");
  params.AddVariable(@"\[Main].[DestinationPath]", 
    @"C:\TestFiles\Archive\Customers.txt");
  params.AddVariable(@"\[Main].[SourcePath]", 
    @"\\nc1\Customers\Customers.txt");
  engine.SetExecutionParameters(parameters);
  engine.ExecuteLiveTestsWithGui("CUSTOMERS", "Main.dtsx");
}

I need an instance of ILiveTestEngine, which I create using EngineFactory. Loading packages is the same as when using IUnitTestEngine. The LoadActions method loads all actions defined in the calling assembly and is practically an equivalent of Load­UnitTests. At this point, however, the similarity with unit tests stops. Instead of executing unit tests, I tell the Test Engine to execute the Main.dtsx package by calling the ExecuteLiveTestsWithGui.

When the Main.dtsx package starts, it runs the CopyCustomers.dtsx by executing the EPT CopyCustomers task. Each successfully finished task in the CopyCustomers.dtsx triggers one of the corresponding action methods in the CopyCustomersLiveTests class. It’s important to note that this test implicitly tests the configuration settings of CopyCustomers.dtsx package.

Configured variables inherit their values from the Main.dtsx package. Please note that these variables are used as connection strings in the flat file connection managers of the CopyCustomers.dtsx package. This basically means that execution success of the tasks in the CopyCustomers.dtsx package depends on whether the value handover between these two packages works properly. This is a simple example of how interactions and dependencies between packages are tested, but you can imagine more complex scenarios where isolated unit tests wouldn’t be enough to cover the test case.

Test Engine Internals

The core class that implements the main functions of the SSISTester library is TestEngine. It’s an internal class that’s exposed through the IUnitTestEngine and ILiveTestEngine interfaces. The two methods that reveal most of the inner logic are LoadUnitTests (shown in Figure 10) and ExecuteUnitTests.

Figure 10 The LoadUnitTests Method

public void LoadUnitTests(){
  Assembly assembly = Assembly.GetCallingAssembly();
  IEnumerable<Type> types = assembly.GetTypes().Where(t => t.GetCustomAttributes(false).OfType<UnitTestAttribute>().Any() && 
    t.BaseType != null && t.BaseType.Name.Equals("BaseUnitTest"));
  foreach (Type t in types)
  {
    var attribute =
      t.GetCustomAttributes(false).OfType<UnitTestAttribute>().Single();
    DtsPackage package =
      _packages[attribute.Repository].GetForName(attribute.PackageName);
    string executable = attribute.ExecutableName;
    bool precedenceTestOnly = attribute.PrecedenceConstraintsTestOnly;
    var test = (BaseUnitTest)Activator.CreateInstance(t);
    test.TestClass = t;
    test.SetTestTargets(package, executable, precedenceTestOnly);
    test.Started += BaseUnitTestStarted;
    test.Finished += BaseUnitTestFinished;
    _unitTests.Add(test);
  }
}

LoadUnitTests basically iterates all classes decorated with the UnitTest attribute and creates an instance for each. These instances are then cast to BaseUnitTest and are assigned the target package previously loaded from the package repository. At the end, all instances are saved in the _unitTests list. The method ExecuteUnitTests iterates all BaseUnitTest instances and calls ExecuteTests on each:

public void ExecuteUnitTests(){
  foreach (BaseUnitTest t in _unitTests){
    t.ExecuteTest();
  }
}

The actual execution of unit tests is implemented in the ExecuteTest method (shown in Figure 11) in the BaseUnitTest class.

Figure 11 The ExecuteTest Method

public void ExecutTest(){
  Result = new UnitTestResult(Package, Executable) { TestOutcome =
    TestOutcome.InProgress, StartedAt = DateTime.Now };
  ExecuteSetup(CreateSetupContext());
  if (!Result.IsSetupSuccess)
    ExecuteTeardown(CreateTeardownContext());
  else{
    if(!PrecedenceOnly)
      Executable.Execute();
    ExecuteVerify(CreateVerifyContext());
    ExecuteTeardown(CreateTeardownContext());
    Result.FinishedAt = DateTime.Now;
  }
}

The most important aspect of this method is that it executes the Setup, Verify and Teardown methods as well as the target package.

Wrapping Up

The examples presented here, along with the accompanying project, should allow you to start testing your SSIS projects. Automating the testing of your SSIS packages can save you a lot of time. What’s more important, automated testing is more reliable because it’s done continuously and you can cover more packages. Once you have written tests, you can always run them during automated build processes. In the end, this means fewer errors and better quality.

Pavle Gudurić is a software engineer located in Germany. He has a master’s degree in e-business and several technical certifications, and develops business intelligence (BI) solutions in the finance industry. Reach him at pavgud@gmail.com.

Thanks to the following technical experts for reviewing this article: Christian Landgrebe (LPA) and Andrew Oakley (Microsoft)
Christian Landgrebe leads the database team at LPA, focused on delivering BI solutions to clients in the financial and banking industry.
Andrew Oakley is a Senior Program Manager on the patterns & practices team. Prior to becoming a Program Manager, Andrew spent two years as a Technical Evangelist for Visual Studio and the .NET platform. His current project focuses on data access guidance around building polyglot persistent systems using relational and NoSQL data stores.