为什么需要用存储过程调用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:
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);
24 INSERT INTO @variables
25 (property_path,
26 property_value)
27 VALUES (N'\Package.Variables[User::var2].Value',
28 @database);
30 INSERT INTO @variables
31 (property_path,
32 property_value)
33 VALUES (N'\Package.Variables[User::var3].Value',
34 @level);
36 INSERT INTO @variables
37 (property_path,
38 property_value)
39 VALUES (N'\Package.Variables[User::var4].Value',
40 @operation_type);
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;
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;
65 BEGIN TRY
66 SELECT @max = ISNULL(MAX(id), 0)
67 FROM @variables;
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
77 DECLARE @var0 SMALLINT
= 1
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
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;
93 EXEC [SSISDB].[catalog].[set_execution_property_override_value]
94 @execution_id,
95 @property_path,
96 @property_value,
97 @sensitive
99 SET @i=@i + 1;
100 END
102 EXEC [SSISDB].[catalog].[start_execution]
103 @execution_id
105 SET @statusReport='Started to execute ' + @package_name;
107 RAISERROR(@statusReport,0,1) WITH NOWAIT;
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;
120 SET @statusReport = 'Running Status is '
121 + CAST(@runningStatus AS VARCHAR(20));
123 RAISERROR(@statusReport,0,1) WITH NOWAIT;
125 IF @runningStatus IS NULL
126 BEGIN
127 RAISERROR('Execution log is not found yet. Wait for 30 seconds...',0,1) WITH NOWAIT;
129 WAITFOR DELAY '00:00:30';
131 SET @isBufferUsed = 1;
132 END
133 ELSE
134 BREAK;
135 END
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;
141 SET @status = 1;
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 + '...';
150 RAISERROR(@statusReport,0,1) WITH NOWAIT;
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
158 RAISERROR(@statusReport,0,1) WITH NOWAIT;
160 SET @status = CASE @runningStatus
161 WHEN 7 THEN 0
162 ELSE 1
163 END;
164 END
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;
170 SET @status = 1;
172 BREAK;
173 END
174 END
175 END TRY
177 BEGIN CATCH
178 SET @errMsg=ERROR_MESSAGE();
179 SET @errSev=ERROR_SEVERITY();
180 SET @errState=ERROR_STATE();
182 RAISERROR(@errMsg,@errSev,@errState) WITH NOWAIT;
184 SET @status = 1;
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;
6 RECONFIGURE;
7 EXEC sp_configure
8 'xp_cmdshell',
9 1;
11 DECLARE @cmd VARCHAR(8000),
12 @returncode INT
13 DECLARE @val1 VARCHAR(255),
14 @val2 VARCHAR(255),
15 @val3 VARCHAR(255)
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 + '"'
27 EXEC @returncode = xp_cmdshell--0:Succeeded 1:Failed
28 @cmd
