Exec SSIS Package from function or sp

Exec SSIS Package from function or sp

https://masudprogrammer.wordpress.com/2016/08/31/ssis-execute-a-pacakge-and-wait-until-package-execution-finished/

The object type is set to either 20 (for a project parameter) or 30 (for a package parameter).

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE dbo.SP_XMLTraceableItems
@DataAreaID nvarchar(5) = 'EXP'
,@Year nvarchar(5) = '2013'
,@Quarter nvarchar(5) = '3'
,@IFNS nvarchar(10) = '4040'
,@PodpFirstName nvarchar(255) = 'Example'
,@PodpSecondName nvarchar(255) = 'Example'
,@PodpFatherName nvarchar(255) = 'Example'
,@Email nvarchar(255) = 'mail@example.com'
AS
BEGIN
SET NOCOUNT ON;

DECLARE
 @FileName nvarchar(255)
,@INN nvarchar(255)
,@KPP nvarchar(255)
,@OrgName nvarchar(255)

SELECT 
 @OrgName = C.NAME
,@INN = C.INN_RU
,@KPP = C.KPPU_RU
FROM VT_STG.dbo.AX_COMPANYINFO C
WHERE C.DATAAREAID = @DataAreaID

SET @FileName = 'EXAMPLE_' + @IFNS + '_' + @IFNS + '_' + @INN + @KPP + '_' + REPLACE(CAST(GETDATE() as DATE), '-', '') + '_'+ CAST(NEWID() as NVARCHAR(50)) + '.xml'

Declare @execution_id bigint
EXEC [SSISDB].[catalog].[create_execution]
 @package_name='Package.dtsx'
,@execution_id=@execution_id OUTPUT
,@folder_name=N'FolderName'
,@project_name=N'SsisProject'
,@use32bitruntime=False
,@reference_id=Null

DECLARE @var0 smallint = 1
EXEC [SSISDB].[catalog].[set_execution_parameter_value]
 @execution_id
,@object_type=50
,@parameter_name=N'LOGGING_LEVEL'
,@parameter_value=@var0

DECLARE @var1 bit = 0
EXEC [SSISDB].[catalog].[set_execution_parameter_value]
 @execution_id
,@object_type=50
,@parameter_name=N'DUMP_ON_ERROR'
,@parameter_value=@var1

exec [SSISDB].[catalog].[set_execution_parameter_value]
 @execution_id
,@object_type=50
,@parameter_name=N'SYNCHRONIZED'
,@parameter_value=1

--DataAreaID
exec [SSISDB].[catalog].[set_execution_parameter_value]
 @execution_id
,@object_type=30
,@parameter_name=N'DataAreaID'
,@parameter_value=@DataAreaID

--email
exec [SSISDB].[catalog].[set_execution_parameter_value]
 @execution_id
,@object_type=30
,@parameter_name=N'email'
,@parameter_value=@Email

--FileName
exec [SSISDB].[catalog].[set_execution_parameter_value]
 @execution_id
,@object_type=30
,@parameter_name=N'FileName'
,@parameter_value=@FileName

--IFNS
exec [SSISDB].[catalog].[set_execution_parameter_value]
 @execution_id
,@object_type=30
,@parameter_name=N'IFNS'
,@parameter_value=@IFNS

--INN
exec [SSISDB].[catalog].[set_execution_parameter_value]
 @execution_id
,@object_type=30
,@parameter_name=N'INN'
,@parameter_value=@INN

--KPP
exec [SSISDB].[catalog].[set_execution_parameter_value]
 @execution_id
,@object_type=30
,@parameter_name=N'KPP'
,@parameter_value=@KPP

--OrgName
exec [SSISDB].[catalog].[set_execution_parameter_value]
 @execution_id
,@object_type=30
,@parameter_name=N'OrgName'
,@parameter_value=@OrgName

--PodpFatherName
exec [SSISDB].[catalog].[set_execution_parameter_value]
 @execution_id
,@object_type=30
,@parameter_name=N'PodpFatherName'
,@parameter_value=@PodpFatherName

--PodpFirstName
exec [SSISDB].[catalog].[set_execution_parameter_value]
 @execution_id
,@object_type=30
,@parameter_name=N'PodpFirstName'
,@parameter_value=@PodpFirstName

--PodpSecondName
exec [SSISDB].[catalog].[set_execution_parameter_value]
 @execution_id
,@object_type=30
,@parameter_name=N'PodpSecondName'
,@parameter_value=@PodpSecondName

--Quarter
exec [SSISDB].[catalog].[set_execution_parameter_value]
 @execution_id
,@object_type=30
,@parameter_name=N'Quarter'
,@parameter_value=@Quarter

--Year
exec [SSISDB].[catalog].[set_execution_parameter_value]
 @execution_id
,@object_type=30
,@parameter_name=N'Year'
,@parameter_value=@Year

EXEC [SSISDB].[catalog].[start_execution] @execution_id
END
GO