I am trying to write integration tests for SSIS packages. The SSIS package has been deployed to the SSIS catalog (SSISDB). I am using a server with the Winders Server 2012 OS, and am using SQL Server 2012
My code is below
EXEC tSQLt.NewTestClass 'testClass'
CREATE PROCEDURE testClass.[test ssis package integration]
AS
BEGIN
IF OBJECT_ID('actual') IS NOT NULL DROP TABLE actual;
IF OBJECT_ID('expected') IS NOT NULL DROP TABLE expected;
/* Assemble
--------------------------------------------------------------------- */
/* Clear source tables */
TRUNCATE TABLE [Database1].[dbo].[table1];
TRUNCATE TABLE [Database1].[dbo].[table2];
/* Clear destination tables */
TRUNCATE TABLE [Database2].[dbo].[table1];
/* Insert set up data */
---
/* Create expected table */
CREATE TABLE expected (
ID INT,
Code NVARCHAR(50)
);
/* Insert expected data */
---
/* Act
--------------------------------------------------------------------- */
/* Execute SSIS package */
DECLARE @execution_id BIGINT;
EXEC [SSISDB].[catalog].[create_execution] @package_name=N'Portfolio.dtsx', @execution_id=@execution_id OUTPUT, @folder_name=N'DDoubleU', @project_name=N'PortfolioMaster', @use32bitruntime=False, @reference_id=Null;
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].[set_execution_parameter_value] @execution_id, @object_type=50, @parameter_name=N'SYNCHRONIZED', @parameter_value=1
EXEC [SSISDB].[catalog].[start_execution] @execution_id;
/* Select record with MAX data load ID because we want the data set just created*/
SELECT [ID], [Code]
INTO actual
FROM [Database2].[dbo].[table1];
/* Assert
--------------------------------------------------------------------- */
EXEC tSQLt.AssertEqualsTable 'expected', 'actual';
/* Clean up
--------------------------------------------------------------------- */
DROP TABLE actual;
DROP TABLE expected;
END;
The issue I have is that when I execute the below command in SSMS
EXEC tSQLt.Run 'testClass';
The SSIS package does not trigger.
Things I have tried so far:
Reading this post on stack - from what I can see I've created my test as described here.
Running the code that is supposed to execute the SSIS package in isolation in SSMS. This works as expected. By this I mean the package executes and the data in the destination table is what I expect.
Adding a WAITFOR DELAY command after the [start_execution] command to make sure that the SSIS package has time to complete, but this doesn't seem to be the issue.
Uninstalling tSQLt and re-installing it.
