使用 tSQLt 对 SSIS 包进行单元测试

2021-12-30 00:00:00 sql-server ssis tsqlt dtexec

我真的很喜欢用 tsqlt 来测试 procs 和函数，但我真的希望能够同时执行 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.

例如，要测试加载到 Staging 中的数据的完整性，测试将是在包加载一个 staging 表之后 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

右键单击并选择执行"

执行包"对话框将打开.

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.

    相关文章