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

Question

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

Please supply the code in question for help with this.

EternalHour
– EternalHour

2015-02-17 09:47:36 +00:00
Commented Feb 17, 2015 at 9:47 — EternalHour
– EternalHour, Commented Feb 17, 2015 at 9:47
I have added the SP which i am trying to call

Prasanna Selvaraju
– Prasanna Selvaraju

2015-02-25 03:12:13 +00:00
Commented Feb 25, 2015 at 3:12 — Prasanna Selvaraju
– Prasanna Selvaraju, Commented Feb 25, 2015 at 3:12

DarkHorse · Accepted Answer · 2017-01-02 02:05:08Z

0

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.

edited Jan 2, 2017 at 2:05

DarkHorse

9731 gold badge10 silver badges29 bronze badges

answered Jan 2, 2017 at 0:41

user7363681

11 bronze badge

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related