Clear Thinking in Data, Cloud, and AI

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

https://www.mssqltips.com/sqlservertip/2992/how-to-execute-an-integration-services-ssis-package-from-a-sql-server-stored-procedure/

Add to favorites