Skip to content
MarkWojciechowicz edited this page Jul 21, 2017 · 3 revisions


SqlTest provides the scaffolding to write tests for SQL Server and SSIS in C# using nunit or another test harness.




The following example shows a SQL Test class. Note that the setup and teardown methods are used to create and rollback each test in a transaction. This is a function of the test harness (nunit) which will call the setup method before each test and the teardown after each test. Because tests are rolled back, they can be written in a destructive way in order to create a consistent setup - truncating or faking tables and views and inserting data.

using System;
using NUnit.Framework;
using SqlTest;
using System.Transactions;

namespace Sample.Tests
public class CustomerLoad
        TransactionScope scope;
	SqlTestTarget dataWarehouseDb;
            SqlTestTarget staging;

    public void Setup()
        scope = new TransactionScope();
        dataWarehouseDb = new SqlTestTarget("DataWarehouse");
        staging= new SqlTest.SqlTestTarget("Staging");

    public void Teardown()
        if (Transaction.Current.TransactionInformation.Status == TransactionStatus.Active)

    public void CustomerLoad_DuplicateCustomer_FirstInstanceIsInserted()
        dataWarehouseDb.ExecuteAdhoc(@"Truncate Dimension.Customer;");
        staging.ExecuteAdhoc(@"Truncate staging.Customer;
                              Insert into Staging.Customer (Id, Name)
                                Values (1, 'Test Customer'), (2, 'Test Customer');"

        staging.ExecuteAdhoc($"Exec Staging.CustomerLoad;");

        var actual = dataWarehouseDb.GetActual("Select top (1) CustomerId from Dimension.Customer Order by 1 desc;");
        Assert.That(actual, Is.EqualTo(1));

You can’t perform that action at this time.