為什么需要用存儲過程調(diào)用SSIS Package？
可以綜合利用存儲過程和SSIS Package各自的優(yōu)點。如Package中有很多現(xiàn)成的component直接使用，而存儲過程中實現(xiàn)同樣功能則需要大費周章；存儲過程的傳參靈活方便，而Package的傳入?yún)?shù)如果是經(jīng)常需要變化的，就比較麻煩了。
兩種方法
1、調(diào)用SSISDB內(nèi)置的存儲過程
如果SSIS Package是部署在SQL Server的Integration Services Catalogs上的話，可以利用SSISDB中的幾個內(nèi)置存儲過程來實現(xiàn)功能。
注意：調(diào)用以此方法寫就的存儲過程時需要用Windows Authentication方式登錄數(shù)據(jù)庫
示例存儲過程用到一個自定義表變量PACKAGEVARIABLES，需要事先定義：
IF NOT EXISTS(SELECT * FROM sys.table_types)
CREATE TYPE [dbo].[PackageVariables] AS TABLE (
[id] INT IDENTITY (1, 1) NOT NULL,
[property_path] NVARCHAR (4000) NOT NULL,
[property_value] NVARCHAR (MAX) NOT NULL,
[sensitive] BIT DEFAULT ((0)) NOT NULL
);
存儲過程實現(xiàn)代碼如下：
CREATE PROC [dbo].[usp_CallSSISPackage] @package_name NVARCHAR(260),--包名稱
@folder_name NVARCHAR(128),--IS Catagory文件夾名
@project_name NVARCHAR(128),--IS Catagory項目名稱
@use32bitruntime BIT=FALSE,--以32位運行還是64位
@delay VARCHAR(100)='00:00:30',--package啟動后每隔多久查看一次運行情況
@maxExecMinutes INT=60,--超時分鐘數(shù)，超過這個時間則不再繼續(xù)等待
@variables PACKAGEVARIABLES READONLY,--自定義數(shù)據(jù)類型
@status INT OUTPUT,--0:Succeeded 1:Failed package運行狀態(tài)
@execution_id BIGINT OUTPUT--SSISDB自動生成的execution_id
AS
BEGIN
/*
--Example:
DECLARE @variables AS PACKAGEVARIABLES;
DECLARE @pkgStatus INT;
DECLARE @execution_id BIGINT;
INSERT INTO @variables
(property_path,
property_value)
VALUES (N'\Package.Variables[User::var1].Value',
@script);
INSERT INTO @variables
(property_path,
property_value)
VALUES (N'\Package.Variables[User::var2].Value',
@database);
INSERT INTO @variables
(property_path,
property_value)
VALUES (N'\Package.Variables[User::var3].Value',
@level);
INSERT INTO @variables
(property_path,
property_value)
VALUES (N'\Package.Variables[User::var4].Value',
@operation_type);
EXEC [dbo].[usp_CallSSISPackage]
@package_name=N'MyPackage.dtsx',
@folder_name=N'MySolution',
@project_name=N'MyProject',
@variables=@variables,
@status=@pkgStatus,
@execution_id=@execution_id;
*/
SET NOCOUNT ON;
DECLARE @property_path NVARCHAR(4000);
DECLARE @property_value NVARCHAR(MAX);
DECLARE @sensitive BIT;
DECLARE @i INT=1;
DECLARE @max INT;
DECLARE @runningStatus INT;
DECLARE @isBufferUsed BIT = 0;
DECLARE @statusReport VARCHAR(500);
DECLARE @pkgStartTime DATETIME = GETDATE();
DECLARE @errMsg NVARCHAR(2048);
DECLARE @errSev INT;
DECLARE @errState INT;
BEGIN TRY
SELECT @max = ISNULL(MAX(id), 0)
FROM @variables;
EXEC [SSISDB].[catalog].[create_execution]
@package_name=@package_name,
@execution_id=@execution_id OUTPUT,
@folder_name=@folder_name,
@project_name=@project_name,
@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
WHILE @i <= @max
BEGIN
SELECT @property_path = property_path,
@property_value = property_value,
@sensitive = sensitive
FROM @variables
WHERE id = @i;
EXEC [SSISDB].[catalog].[set_execution_property_override_value]
@execution_id,
@property_path,
@property_value,
@sensitive
SET @i=@i + 1;
END
EXEC [SSISDB].[catalog].[start_execution]
@execution_id
SET @statusReport='Started to execute ' + @package_name;
RAISERROR(@statusReport,0,1) WITH NOWAIT;
--Monitor the log
WHILE @runningStatus IN( 1, 2, 5, 8 )
OR @runningStatus IS NULL
BEGIN
WHILE @runningStatus IS NOT NULL
OR @isBufferUsed = 0
BEGIN
SELECT @runningStatus = [Status]
FROM SSISDB.[catalog].[executions] WITH(NOLOCK)
WHERE execution_id = @execution_id;
SET @statusReport = 'Running Status is '
+ CAST(@runningStatus AS VARCHAR(20));
RAISERROR(@statusReport,0,1) WITH NOWAIT;
IF @runningStatus IS NULL
BEGIN
RAISERROR('Execution log is not found yet. Wait for 30 seconds...',0,1) WITH NOWAIT;
WAITFOR DELAY '00:00:30';
SET @isBufferUsed = 1;
END
ELSE
BREAK;
END
IF @runningStatus IS NULL
BEGIN
RAISERROR('Execution log is not found after waiting for 30 seconds. Please check the status and update ProcessFlag mannually!',0,1) WITH NOWAIT;
SET @status = 1;
BREAK;
END
ELSE IF @runningStatus IN( 1, 2, 5, 8 )
BEGIN
SET @statusReport = 'The package is ' + CASE @runningStatus WHEN 1 THEN 'created' WHEN 2 THEN 'running' WHEN 5 THEN 'pending' WHEN 8 THEN 'stopping' END + '. Wait for ' + @delay
+ '...';
RAISERROR(@statusReport,0,1) WITH NOWAIT;
WAITFOR DELAY @delay;
END
ELSE
BEGIN
SET @statusReport = 'The package is ' + CASE @runningStatus WHEN 3 THEN 'canceled' WHEN 4 THEN 'failed' WHEN 6 THEN 'ended unexceptedly' WHEN 7 THEN 'succeeded' WHEN 9 THEN 'completed' END
RAISERROR(@statusReport,0,1) WITH NOWAIT;
SET @status = CASE @runningStatus
WHEN 7 THEN 0
ELSE 1
END;
END
IF DATEDIFF(MINUTE, @pkgStartTime, GETDATE()) >= @maxExecMinutes
BEGIN
RAISERROR('The package execution timed out! Please check the status and update ProcessFlag mannually!',0,1) WITH NOWAIT;
SET @status = 1;
BREAK;
END
END
END TRY
BEGIN CATCH
SET @errMsg=ERROR_MESSAGE();
SET @errSev=ERROR_SEVERITY();
SET @errState=ERROR_STATE();
RAISERROR(@errMsg,@errSev,@errState) WITH NOWAIT;
SET @status = 1;
RETURN;
END CATCH
END
2、調(diào)用dtexec命令
如果SSIS Package是以File System形式存放，則需要調(diào)用dtexec命令了。
雖然這種方法對于package部署在IS Catagory上的情況也適用，但由于無法直接通過return code來判斷package運行成功與否，不推薦。
存儲過程的核心代碼如下：
--Config for dtexec
EXEC sp_configure
'show advanced options',
1;
RECONFIGURE;
EXEC sp_configure
'xp_cmdshell',
1;
DECLARE @cmd VARCHAR(8000),
@returncode INT
DECLARE @val1 VARCHAR(255),
@val2 VARCHAR(255),
@val3 VARCHAR(255)
SET @val1 = 'Value 1'
SET @val2 = 'Value 2'
SET @val3 = 'Value 3'
SET @cmd1='dtexec /Rep e /FILE "\"E:\MySolution\MyProject\MyPackage.dtsx\"" /SET \Package.Variables[User::var1].Value;"'
+ @val1
+ '" /SET \Package.Variables[User::var2].Value;"'
+ @val2
+ '" /SET \Package.Variables[User::var3].Value;"'
+ @val3 + '"'
EXEC @returncode = xp_cmdshell--0:Succeeded 1:Failed
@cmd
