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?

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.