使用tSQLt的SSIS包的单元测试 [英] Unit testing of SSIS Package with tSQLt

问题描述

我真的很喜欢tsqlt来测试proc和函数，但是真的很希望能够执行SSIS程序包并利用FakeTable和AssertEquals来确定SSIS程序包是否达到了预期的功能.

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.

有人探索过这条路吗，有可能通过tsqlt将测试包装在其中的事务调用dtexec吗?

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

推荐答案

我相信我可以回答您Andrey的问题，尽管这来得有点晚.但是我相信它将使他人受益.

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

我们正在使用RedGate SQLTest(tSQLt)进行数据质量测试，这是我们集成测试的一部分.

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

例如，要测试正在加载到暂存中的数据的完整性，要测试的是在程序包加载暂存表之后对AssertEqualsTable进行测试.这是事物的基本顺序:

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:

组装

• 使用数据创建并加载期望的表.

行为

• 通过t-sql执行目录中的SSIS包.您可以生成t-sql代码来调用目录中的任何程序包，如下所示:

• 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'

生成了从存储过程或脚本执行程序包所需的所有t-SQL代码:

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

返回到您的测试存储过程，并将代码粘贴到"Act"部分.

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

声明 -从要测试的程序包的SSIS目标表中选择实际表.

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';

这就是全部.

在示例数据库中查看外键测试，以指导您进行外键和参照完整性测试.

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.

这篇关于使用tSQLt的SSIS包的单元测试的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！