You can run SSIS package from stored procedure. Follow this;
ALTER PROCEDURE EXECUTE_SSIS_PACKAGE_DEMO
@OUTPUT_EXECUTION_ID bigint output
AS
BEGIN
DECLARE @EXECUTION_ID BIGINT
EXEC SSISDB.CATALOG.CREATE_EXECUTION
@FOLDER_NAME = 'MyFolderName'
,@PROJECT_NAME = 'MyProjectName'
,@PACKAGE_NAME = 'MyPackageName.DTSX'
,@USE32BITRUNTIME=FALSE
,@REFERENCE_ID=1 --This is environment ID
,@RUNINSCALEOUT=FALSE
,@EXECUTION_ID = @EXECUTION_ID OUTPUT
EXEC SSISDB.CATALOG.START_EXECUTION @EXECUTION_ID
SET @OUTPUT_EXECUTION_ID = @EXECUTION_ID
END
You can execute this procedure;
--EXECUTE STORED PROCEDURE
DECLARE @OUTPUT_EXECUTION_ID BIGINT
EXEC DBO.EXECUTE_SSIS_PACKAGE_DEMO @OUTPUT_EXECUTION_ID OUTPUT
PRINT @OUTPUT_EXECUTION_ID
You can check the status of execution;
--CHECK STATUS
SELECT STATUS
FROM SSISDB.CATALOG.EXECUTIONS
WHERE EXECUTION_ID = N''
SSIS package execution requires windows authentication. My client has SQL server authentication enabled so I can not take advantage of this approach.
In my next article, I will show you how to run SSIS Packages using SQL authentication.
Resources
