Executing an SSIS Task from a SQL Authentication Login

• Thom A

  SSC Guru

  Points: 99137

  More actions

  February 23, 2017 at 9:02 am

  #319006

  Title really says it all here, I need to get an SSIS task, held within SSISDB, to be executed by a SQL Authentication login. I've tried using EXECUTE AS LOGIN, however, I still receive 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.
  Does anyone have any ideas?
  (Anonymised) SQL I currently have:

  USE SSISDB;
GO

  EXECUTE AS LOGIN = 'MyDomain\SSISProxy';

  DECLARE @execution_id bigint;
EXEC SSISDB.catalog.create_execution @package_name=N'Get Reference OA Docs.dtsx', @execution_id=@execution_id OUTPUT, @folder_name=N'SSIS Packages', @project_name=N'MyProject', @use32bitruntime=False, @reference_id=NULL;
--Select @execution_id;
DECLARE @var0 sql_variant = N'0';
EXEC SSISDB.catalog.set_execution_parameter_value @execution_id, @object_type=30, @parameter_name=N'Branch', @parameter_value=@var0;
DECLARE @var1 bit = 1;
EXEC SSISDB.catalog.set_execution_parameter_value @execution_id, @object_type=30, @parameter_name=N'Quote', @parameter_value=@var1;
DECLARE @var2 sql_variant = N'XXXX001XX1';
EXEC SSISDB.catalog.set_execution_parameter_value @execution_id, @object_type=30, @parameter_name=N'Reference', @parameter_value=@var2;
DECLARE @var3 smallint = 1;
EXEC SSISDB.catalog.set_execution_parameter_value @execution_id, @object_type=50, @parameter_name=N'LOGGING_LEVEL', @parameter_value=@var3;
EXEC SSISDB.catalog.start_execution @execution_id;
REVERT;


  Thanks all.

  Thom~

  Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
  Larnu.uk

• tim.ffitch 25252

  SSCrazy

  Points: 2055

  More actions

  February 27, 2017 at 8:45 am

  #1930482

  Maybe you could use an SQL Agent Job to run the SSIS package which itself is started using sp_start_job

  https://msdn.microsoft.com/en-GB/library/ms186757.aspx

• prathibak

  SSC Enthusiast

  Points: 178

  More actions

  October 21, 2020 at 6:28 pm

  #3799825

  Hi SSC Guru,

  I have same problem. Can you please tell us how you solved it. We can't use SQL Job because you can't pass parameters to sql job.

• hedayat

  Newbie

  Points: 2

  More actions

  November 18, 2024 at 9:51 am

  #4485302

  Change the db owner to administrator. Here are the steps I took to solve this issue:

  1- Right-Click on the SSISDB database and select properties

  2- Click on Files under the Select a page

  3- Under the Owner, but just below the Database Name on the right-hand pane, select [pc name]/Administrator as the owner.

  Attachments:

  You must be logged in to view attached files.