Exec SSIS Package from function or sp
- tags
- #SQLServer #SSIS
- categories
- SQLServer SSIS
Exec SSIS Package from function or sp
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