CODEWITHSUNDEEP
ssiscatalog
Prasanna Selvaraju
Prasanna Selvaraju

Reputation: 1

Cannot Run [catalog].[create_execution] stored procedure using SSIS package

I am facing following error in calling SSID DB catalog stored procedure from SSIS Package using windows authentication / sql authentication as well.

ERROR DETAILS :

[Execute SQL Task] Error: Executing the query "EXEC [SSISDB].[catalog].[create_execution] ?, ?,..." failed with the following error: "The operation cannot be started by an account that uses SQL Server Authentication. Start the operation with an account that uses Windows Authentication.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Basically I am trying to make a master package to run all packages from a custom table list in order using FOR EACH LOOP and then capture error using the EXECUTION ID for the same by checking EVEN - OnERROR Logging in same catalog tables .

But in the phase of calling the stored procedure I am stuck with this problem. Please help and guide me. How can I run this package using the catalog stored procedure to run packages in an order make List of packages deployed in SSIS catalog project to run in order?

Suggestions are welcome

SP CODE :

ALTER PROCEDURE [Publish].[usp_ExecutePackages] @PackageName NVARCHAR(255) , @ProjectFolder NVARCHAR(255) ,
@ProjectName NVARCHAR(255) AS BEGIN SET NOCOUNT ON; DECLARE @Execution_id BIGINT ; EXEC [SSISDB].[catalog].[create_execution] @package_name= @PackageName, @execution_id= @Execution_id OUTPUT,
@folder_name= @ProjectFolder, @project_name= @ProjectName,
@use32bitruntime=True;

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

Upvotes: 0

Views: 2106

Answers (1)

user7363681
user7363681

Reputation: 1

The answer is in the Error Message:

error: "The operation cannot be started by an account that uses SQL Server Authentication. Start the operation with an account that uses Windows Authentication.

If the account you logged onto SQL with was not a Windows Authentication account, then calling SSIS from a TSQL stored proc does not work. This is because SSIS is an external windows process & so it doesn't recognise Logins that only exist within the SQL Master database.

The solution: Use an existing AD Account or create a new local account. Create a new Login on SQL & specify Windows Authentication. Then login with it.

Upvotes: 0

Related Questions