Running an SSIS package from a tSQLt test

jchiking

Is it possible to run an SSIS package from within a test? This code runs fine in a normal query window, until I place it into a SQL Test script...

DECLARE @execution_id BIGINT
EXEC [SSISDB].[catalog].[create_execution] @package_name=N'MyPackage.dtsx', @execution_id=@execution_id OUTPUT, @folder_name=N'MyFolder', @project_name=N'MyProject', @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].[set_execution_parameter_value] @execution_id,  @object_type = 50, @parameter_name = N'SYNCHRONIZED', @parameter_value = 1

DECLARE @var1 bit = 0
EXEC [SSISDB].[catalog].[set_execution_parameter_value] @execution_id,  @object_type=50, @parameter_name=N'DUMP_ON_ERROR', parameter_value=@var1 

EXEC [SSISDB].[catalog].[start_execution] @execution_id

Onyxia

We are having a similar issue. When we try to execute a package from a tSQLt test, using the SQL Test package runner it fails. When we run the SP from an SSMS query window, everything works and the SSIS package is executed. :shock: We've added print statements around each side of the package execute statement to confirm the test doesn't just shut down at the execute step in the stored procedure. We can't tell how or why the execute package step is failing, but it is because the tables are not being populated with data from the package. We thought it might be a permissions issue, but nothing in our logging indicates a security context issue. Although we still feel there is some security issue we are not able to see.

Any help or suggestions would be welcome. Anyone out there able to execute MS SQL SERVER 2012 SSIS packages from within SQL Test? :?

David Atkinson

For questions about the tSQLt framework (rather than the SQL Test UI component), please post them to the tSQLt forum:

https://groups.google.com/forum/#!forum/tsqlt

Many thanks,

David Atkinson
Redgate