Search code examples
sql-serverssissql-server-2017

Execute package task with external reference to SQL Server

** Edit: Executing the package with T-SQL script causes an authentication problem: "Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'." **

** Edit 2: This error is caused by Windows credentials performing a double hop. Here is a solution to this problem **

I'm trying to execute an SSIS package that was already deployed to an SQL Server instance before. This SQL Server instance's version is 14.0.3281 (SQL Server 2017).

I can't use project reference as the wanted package is in another project, and I prefer not to upload it to the server's file system.

Is it really a version problem? How can I execute a deployed package from another project?

I know I can run the a job with this package with T-SQL, but I want the task to continue only after this package has successfully completed its execution.

This is the error I get:

The attempted operation is not supported with this database version.

------------------------------ ADDITIONAL INFORMATION:

The attempted operation is not supported with this database version.

Visual Studio Error

Solution

  • There is a way to run a package via T-SQL and wait for it to finish using built-in procedures.

    -- Create the execution object
DECLARE @execution_id BIGINT
EXEC [SSISDB].[catalog].[create_execution] 
    @package_name = @PackageName
    , @project_name = @ProjectName
    , @folder_name = @FolderName
    , @use32bitruntime = False
    , @reference_id = NULL
    , @execution_id = @execution_id OUTPUT

-- Configure execution
EXEC [SSISDB].[catalog].[set_execution_parameter_value] 
    @execution_id
    , @object_type = 50 -- System parameter
    , @parameter_name = N'SYNCHRONIZED'
    , @parameter_value = 1 -- set this to 1 to wait for completion

-- Start the execution
EXEC [SSISDB].[catalog].[start_execution] @execution_id

    You could wrap this into a stored procedure like "startPackage" and also include checking for the result using the execution id:

    -- Check package status, and fail script if the package failed
IF 7 <> (SELECT [status] FROM [SSISDB].[catalog].[executions] WHERE execution_id = @execution_id) AND @Syncronized = 1
        RAISERROR('The package failed!', 16, 1)