Running an SSIS package from a tSQLt test

jchikingjchiking Posts: 2
edited August 1, 2017 12:34PM in SQL Test
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
· Share on Twitter

Comments

  • OnyxiaOnyxia Posts: 1
    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? :?
    · Share on Twitter
  • David AtkinsonDavid Atkinson Posts: 1,462 Rose Gold 2
    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
    David Atkinson
    Product Manager
    Redgate Software
    · Share on Twitter
Sign In or Register to comment.