Unit testing of SSIS Package with tSQLt

I really like tsqlt to test procs and functions, but really would like to be able to also execute SSIS packages and take advantage of FakeTable and AssertEquals to determine if it was the SSIS package did what it was supposed to.

Has anyone explored this path, is it possible to call dtexec from with the transaction that tsqlt wraps your test in?

回答1:

I believe I can answer your question Andrey, although this is a little late in coming. But I believe that it will benefit others.

We are using RedGate SQLTest(tSQLt) to do data quality testing as a part of our integration testing.

For example to test the completeness of the data being loaded into Staging, on test would be to AssertEqualsTable after a package loads a staging table. Here is the basic order of things:

Assemble

Create and load the expected table with data.

Act

Execute the SSIS Package in the catalog via t-sql. You can generate t-sql code to call any package in the catalog as follows:
Locate the package you're testing in it's folder in the catalog
Right click and select 'Execute'
The Execute Package dialogue box will open.
Click the scripting dropdown and select 'Script to Clipboard'

All the t-SQL Code needed to execute the package from a stored procedure or script is generated:

DECLARE @execution_id BIGINT
EXEC [SSISDB].[catalog].[create_execution] 
      @package_name=N'HistoricalLoad_import_rti_stores_s1.dtsx'
    , @execution_id=@execution_id OUTPUT
    , @folder_name=N'Testing'
    , @project_name=N'Staging1_HistoricalLoad_RTIStores'
    , @use32bitruntime=FALSE
    , @reference_id=NULL

SELECT @execution_id

DECLARE @var0 SMALLINT = 1
EXEC [SSISDB].[catalog].[set_execution_parameter_value] 
      @execution_id
    , @object_type=50
    , @parameter_name=N'LOGGING_LEVEL'
    , @parameter_value=@var0

EXEC [SSISDB].[catalog].[start_execution] @execution_id

Go back to your test stored proc and paste the code into the Act section.

Assert - Select into the actual table from the SSIS destination table of the package being tested.

then validate that the expected and actual are equal

EXEC tSQLt.AssertEqualsTable 'expected', 'actual';

And that's all there is too it.

Take a look at the foreign key tests in the examples database to guide you on foreign key and referential integrity tests.

I've found it to be invaluable as a means of regression testing our data warehouse load functionality and also validating our orchestration. Because if we can verify that the data is flowing into the right place, at the right time, then things are executing as expected.

回答2:

tSQLt is a Unit Testing Framework and it is designed for testing code in isolation. So for testing how your code/data will be integrated with the other code/data typically used different types of tests - Integration Tests.

LATER UPDATE

Not exactly about the topic but it may be useful information about unit/integration testing of SSIS packages

回答3:

There is a sample project with unit tests for SSIS at http://ssistester.codeplex.com/. Few samples show the use of the FakeSource and the FakeDestination to assert if data flow streams correctly read/write data.