February 23, 2017 at 9:02 am
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
February 27, 2017 at 8:45 am
Maybe you could use an SQL Agent Job to run the SSIS package which itself is started using sp_start_job
October 21, 2020 at 6:28 pm
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.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply