Unit Testing of an SSIS Package with tSQLt

Question

I really like that tsqlt checks proc and functions, but really would like to execute SSIS packages as well and use FakeTable and AssertEquals to determine if it was an SSIS package, what it should have done.

Has anyone studied this way, is it possible to call dtexec with a transaction that tsqlt completes your test?

Answer 1

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

We use RedGate SQLTest (tSQLt) to test data quality as part of our integration testing.

For example, to check the completeness of the data loaded into Staging, during testing there will be AssertEqualsTable after the package loads the staging table. Here is the basic order of things:

To collect

• Create and load the expected data table.

Law

• Run the SSIS package in the directory through t-sql. You can create t-sql code to call any package in the directory as follows:

• Find the package you are testing in its folder in the directory

• Right-click and select Run

• The Execute Package dialog box opens.

• Click the scripts drop-down menu and select "Script to clipboard"

• All the t-SQL code needed to execute the package from the 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 the test saved process and paste the code into the Act section.

Approve - Select the actual table from the SSIS destination table of the package under test.

• then confirm that expected and actual are equal

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

And all this too.

Take a look at the foreign key tests in the sample database to help you verify the foreign key and reference integrity tests.

I found it invaluable as a means of regression testing our data warehouse loading functions, as well as checking our orchestration. Because if we can verify that the data is flowing into the right place, at the right time, everything happens as expected.

Answer 2

tSQLt is a Unit Testing Framework and is designed to test code in isolation . So, to check how your code / data will be integrated with other code / data commonly used by different types of tests - Integration tests .

LATER UPDATE

Not really about the topic, but there may be useful information about unit / integration testing of SSIS packages

Answer 3

There is an example unit test project for SSIS at http://ssistester.codeplex.com/ . Few samples show the use of FakeSource and FakeDestination to assert that data stream streams read / write data correctly.