SQL SERVER – EXECUTE SSIS PACKAGE FROM T-SQL

  

/*

GET THE ENVIRONMENT_REFERENCESID WHICH IS CONFIGURED FOR PROJECT

*/

DECLARE @REFERENCE_ID INT

     SELECT @REFERENCE_ID = REFERENCE_ID

           FROM SSISDB.[INTERNAL].[ENVIRONMENT_REFERENCES] ER WITH (NOLOCK)

           JOIN SSISDB.[CATALOG].[PROJECTS] P WITH (NOLOCK) ON ER.PROJECT_ID = P.PROJECT_ID

           JOIN SSISDB.[CATALOG].[FOLDERS] F WITH (NOLOCK) ON P.FOLDER_ID = F.FOLDER_ID

           WHERE F.NAME = 'FOLDERNAME' AND P.NAME = 'PROJECTNAME'    

 /*

GENERATE EXECUTIONID BY PASSING REQUIRED INFORMATION LIKE PACKAGENAME,FOLDERNAME,PROJECTNAME AND ENVIRONMENT_REFERENCESID

*/ 

     DECLARE @EXECUTION_ID BIGINT

     EXEC [SSISDB].[CATALOG].[CREATE_EXECUTION] @PACKAGE_NAME=N'PACKAGE1.DTSX', @EXECUTION_ID=@EXECUTION_ID OUTPUT, @FOLDER_NAME=N'FOLDERNAME', @PROJECT_NAME=N'PROJECTNAME', @USE32BITRUNTIME=FALSE, @REFERENCE_ID=@REFERENCE_ID

     SELECT @EXECUTION_ID

/*

PASS THE PARAMETER VALUES DYNAMICALLY IN T-SQL . HERE I AM PASSING @VARIABLE1 VALUE TO PARAMETERNAME1 LIKE BELOW

*/

     DECLARE @VAR0 INT = @VARIABLE1

     EXEC [SSISDB].[CATALOG].[SET_EXECUTION_PARAMETER_VALUE] @EXECUTION_ID,  @OBJECT_TYPE=20, @PARAMETER_NAME=N'PARAMETERNAME1', @PARAMETER_VALUE=@VAR0

/*

EXECUTE SSIS PACKAGE FROM T-SQL STATEMENT

*/  

     EXEC [SSISDB].[CATALOG].[START_EXECUTION] @EXECUTION_ID   

• email
• facebook
• linkedin
• twitter
• reddit
• google+

Kiran Reddy A

Kiran has been a part of the IT industry for more than six years and Involved in many MSBI projects and product development with extensive usage of ETL and Reporting tools like SQL Server Integration Services (SSIS), SQL Server Analysis Services (SSAS), SQL Server Reporting Service (SSRS) and T-SQL

Get Free Email Updates to your Inbox!

• Rss
• Twitter
• FaceBook
• Google +