T-SQL存储过程调用SSIS Package
为什么需要用存储过程调用SSIS Package？
可以综合利用存储过程和SSIS Package各自的优点。如Package中有很多现成的component直接使用，而存储过程中实现同样功能则需要大费周章；存储过程的传参灵活方便，而Package的传入参数如果是经常需要变化的，就比较麻烦了
两种方法
1.调用SSISDB内置的存储过程
如果SSIS Package是部署在SQL Server的Integration Services Catalogs上的话，可以利用SSISDB中的几个内置存储过程来实现功能。
注意：调用以此方法写就的存储过程时需要用Windows Authentication方式登录数据库
示例存储过程用到一个自定义表变量PACKAGEVARIABLES，需要事先定义：
1 IF NOT EXISTS(SELECT * 2 FROM sys.table_types) 3 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);
存储过程实现代码如下：
1 CREATE PROC [dbo].[usp_CallSSISPackage] @package_name NVARCHAR(260),--包名称 2 @folder_name NVARCHAR(128),--IS Catagory文件夹名 3 @project_name NVARCHAR(128),--IS Catagory项目名称 4 @use32bitruntime BIT=FALSE,--以32位运行还是64位 5 @delay VARCHAR(100)='00:00:30',--package启动后每隔多久查看一次运行情况 6 @maxExecMinutes INT=60,--超时分钟数，超过这个时间则不再继续等待 7 @variables PACKAGEVARIABLES READONLY,--自定义数据类型 8 @status INT OUTPUT,--0:Succeeded 1:Failed package运行状态 9 @execution_id BIGINT OUTPUT--SSISDB自动生成的execution_id 10 AS 11 BEGIN 12 /* 13 --Example: 14 15 DECLARE @variables AS PACKAGEVARIABLES; 16 DECLARE @pkgStatus INT; 17 DECLARE @execution_id BIGINT; 18 INSERT INTO @variables 19 (property_path, 20 property_value) 21 VALUES (N'\Package.Variables[User::var1].Value', 22 @script); 23 24 INSERT INTO @variables 25 (property_path, 26 property_value) 27 VALUES (N'\Package.Variables[User::var2].Value', 28 @database); 29 30 INSERT INTO @variables 31 (property_path, 32 property_value) 33 VALUES (N'\Package.Variables[User::var3].Value', 34 @level); 35 36 INSERT INTO @variables 37 (property_path, 38 property_value) 39 VALUES (N'\Package.Variables[User::var4].Value', 40 @operation_type); 41 42 EXEC [dbo].[usp_CallSSISPackage] 43 @package_name=N'MyPackage.dtsx', 44 @folder_name=N'MySolution', 45 @project_name=N'MyProject', 46 @variables=@variables, 47 @status=@pkgStatus, 48 @execution_id=@execution_id; 49 */ 50 SET NOCOUNT ON; 51 52 DECLARE @property_path NVARCHAR(4000); 53 DECLARE @property_value NVARCHAR(MAX); 54 DECLARE @sensitive BIT; 55 DECLARE @i INT=1; 56 DECLARE @max INT; 57 DECLARE @runningStatus INT; 58 DECLARE @isBufferUsed BIT = 0; 59 DECLARE @statusReport VARCHAR(500); 60 DECLARE @pkgStartTime DATETIME = GETDATE(); 61 DECLARE @errMsg NVARCHAR(2048); 62 DECLARE @errSev INT; 63 DECLARE @errState INT; 64 65 BEGIN TRY 66 SELECT @max = ISNULL(MAX(id), 0) 67 FROM @variables; 68 69 EXEC [SSISDB].[catalog].[create_execution] 70 @package_name=@package_name, 71 @execution_id=@execution_id OUTPUT, 72 @folder_name=@folder_name, 73 @project_name=@project_name, 74 @use32bitruntime=False, 75 @reference_id=NULL 76 77 DECLARE @var0 SMALLINT = 1 78 79 EXEC [SSISDB].[catalog].[set_execution_parameter_value] 80 @execution_id, 81 @object_type=50, 82 @parameter_name=N'LOGGING_LEVEL', 83 @parameter_value=@var0 84 85 WHILE @i <= @max 86 BEGIN 87 SELECT @property_path = property_path, 88 @property_value = property_value, 89 @sensitive = sensitive 90 FROM @variables 91 WHERE id = @i; 92 93 EXEC [SSISDB].[catalog].[set_execution_property_override_value] 94 @execution_id, 95 @property_path, 96 @property_value, 97 @sensitive 98 99 SET @i=@i + 1; 100 END 101 102 EXEC [SSISDB].[catalog].[start_execution] 103 @execution_id 104 105 SET @statusReport='Started to execute ' + @package_name; 106 107 RAISERROR(@statusReport,0,1) WITH NOWAIT; 108 109 --Monitor the log 110 WHILE @runningStatus IN( 1, 2, 5, 8 ) 111 OR @runningStatus IS NULL 112 BEGIN 113 WHILE @runningStatus IS NOT NULL 114 OR @isBufferUsed = 0 115 BEGIN 116 SELECT @runningStatus = [Status] 117 FROM SSISDB.[catalog].[executions] WITH(NOLOCK) 118 WHERE execution_id = @execution_id; 119 120 SET @statusReport = 'Running Status is ' 121 + CAST(@runningStatus AS VARCHAR(20)); 122 123 RAISERROR(@statusReport,0,1) WITH NOWAIT; 124 125 IF @runningStatus IS NULL 126 BEGIN 127 RAISERROR('Execution log is not found yet. Wait for 30 seconds...',0,1) WITH NOWAIT; 128 129 WAITFOR DELAY '00:00:30'; 130 131 SET @isBufferUsed = 1; 132 END 133 ELSE 134 BREAK; 135 END 136 137 IF @runningStatus IS NULL 138 BEGIN 139 RAISERROR('Execution log is not found after waiting for 30 seconds. Please check the status and update ProcessFlag mannually!',0,1) WITH NOWAIT; 140 141 SET @status = 1; 142 143 BREAK; 144 END 145 ELSE IF @runningStatus IN( 1, 2, 5, 8 ) 146 BEGIN 147 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 148 + '...'; 149 150 RAISERROR(@statusReport,0,1) WITH NOWAIT; 151 152 WAITFOR DELAY @delay; 153 END 154 ELSE 155 BEGIN 156 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 157 158 RAISERROR(@statusReport,0,1) WITH NOWAIT; 159 160 SET @status = CASE @runningStatus 161 WHEN 7 THEN 0 162 ELSE 1 163 END; 164 END 165 166 IF DATEDIFF(MINUTE, @pkgStartTime, GETDATE()) >= @maxExecMinutes 167 BEGIN 168 RAISERROR('The package execution timed out! Please check the status and update ProcessFlag mannually!',0,1) WITH NOWAIT; 169 170 SET @status = 1; 171 172 BREAK; 173 END 174 END 175 END TRY 176 177 BEGIN CATCH 178 SET @errMsg=ERROR_MESSAGE(); 179 SET @errSev=ERROR_SEVERITY(); 180 SET @errState=ERROR_STATE(); 181 182 RAISERROR(@errMsg,@errSev,@errState) WITH NOWAIT; 183 184 SET @status = 1; 185 186 RETURN; 187 END CATCH 188 END
2.调用dtexec命令
如果SSIS Package是以File System形式存放，则需要调用dtexec命令了。
虽然这种方法对于package部署在IS Catagory上的情况也适用，但由于无法直接通过return code来判断package运行成功与否，不推荐。
存储过程的核心代码如下：
1 --Config for dtexec 2 EXEC sp_configure 3 'show advanced options', 4 1; 5 6 RECONFIGURE; 7 EXEC sp_configure 8 'xp_cmdshell', 9 1; 10 11 DECLARE @cmd VARCHAR(8000), 12 @returncode INT 13 DECLARE @val1 VARCHAR(255), 14 @val2 VARCHAR(255), 15 @val3 VARCHAR(255) 16 17 SET @val1 = 'Value 1' 18 SET @val2 = 'Value 2' 19 SET @val3 = 'Value 3' 20 SET @cmd1='dtexec /Rep e /FILE "\"E:\MySolution\MyProject\MyPackage.dtsx\"" /SET \Package.Variables[User::var1].Value;"' 21 + @val1 22 + '" /SET \Package.Variables[User::var2].Value;"' 23 + @val2 24 + '" /SET \Package.Variables[User::var3].Value;"' 25 + @val3 + '"' 26 27 EXEC @returncode = xp_cmdshell--0:Succeeded 1:Failed 28 @cmd
