如何从存储过程中调用 SSIS 包

• 下载 enablexp_cmdScript - 251 B
• 下载 ssisfromsql - 570 B

引言

SSIS (SQL Server Integration Services) 包是在服务器端执行的包，可以从服务器调用，这可以通过创建 Web 服务来实现。但有时，我们希望将一些 Excel 文件或平面文件传递到 SSIS 包中，而这些文件必须传输到服务器才能在 SSIS 包中使用。

因此，有时当 Web 服务被限制使用服务器上的资源时，可能会出现一些安全问题。所以我们必须使用其他方式，而不是 Web 服务，来调用 SSIS。

背景

本文假设您熟悉创建 SSIS 包，如何向包添加变量以及如何调用 SSIS 包以在代码中使用。

Using the Code

本文有两个附件

1. enablexp_cmdScript.sql
2. ssisfromsql.sql

首先，我将介绍除使用“Web 服务”之外的另一种调用 SSIS 包的方法。我们可以使用存储过程来调用 SSIS 包。如何做？

SQL Server 2005 中有一个系统存储过程叫做“xp_cmdshell”，它默认被设置为“False”，这意味着在 SQL Server 安装时该存储过程是未激活的。我们必须手动启用它才能使用。这可以通过两种方式完成：一种是运行一些脚本（在 enablexp_cmdscript.sql 文件中提供），另一种是使用 SQL Server 2005 一起安装的“SQL Server 表面配置”工具。

xp_cmdshell：“xp_cmdshell”是 Microsoft 提供的一个扩展存储过程，存储在 master 数据库中。该过程允许您通过 T-SQL 代码直接向 Windows 命令行发出操作系统命令。如果需要，这些命令的输出将返回给调用例程。

从您的 SQL Server 安装的“程序”菜单中启动“表面配置”工具。它看起来会像这样

现在，点击“表面配置功能”链接，您将看到以下屏幕。从左侧菜单中，选择您的实例名称，然后在下面点击“xp_cmdshell”选项，就像这样

只需启用 xp_cmdshell 选项，在您重启 SQL Server 服务后，xp_cmdshell SP 将被启用。

如果您不想这样做，只需在 SQL Server 中选定的实例上运行以下脚本行

USE master 
GO 
EXEC sp_configure 'show advanced options', 1 
GO 
RECONFIGURE WITH OVERRIDE 
GO 
EXEC sp_configure 'xp_cmdshell', 1 
GO 
RECONFIGURE WITH OVERRIDE 
GO 
EXEC sp_configure 'show advanced options', 0 
GO        

现在，我们准备好使用“xp_cmdshell”存储过程来调用我们的 SSIS 包了。

现在，我创建了一个名为“ImportItemFile”的 SSIS 包，它的功能是从服务器上提供的位置提取 Excel 文件，并将 Excel 文件中的所有项加载到数据库的 Item 表中。

我需要传递的**变量**是：FileName、CreatedBy、ContractDbConnectionString、BatchID、SupplierID。

在这里，我使用了两个特殊命令：一个是“xp_cmdshell”，第二个是“dtexec”。
那么“dtexec”命令是什么？

dtexec：dtexec 命令行实用程序用于配置和执行 SQL Server 2005 Integration Services (SSIS) 包。dtexec 实用程序提供对所有包配置和执行功能的访问，例如连接、属性、变量、日志记录和进度指示器。dtexec 实用程序允许您从三个来源加载包：Microsoft SQL Server 数据库、SSIS 服务和文件系统。
(参考自：http://msdn.microsoft.com/en-us/library/ms162810.aspx)

现在我将创建的脚本是动态 SQL，这意味着我们可以用它来调用任何 SSIS 包，我们只需要传递必要的变量。

declare @ssisstr varchar(8000), @packagename varchar(200),@servername varchar(100)
declare @params varchar(8000)
----my package name
set @packagename = 'ImportItemFile'
----my server name
set @servername = 'myserver\sql2k5'

---- please make this line in single line, I have made this line in multiline 
----due to article format.
----package variables, which we are passing in SSIS Package.
set @params = '/set \package.variables[FileName].Value;"\"\\127.0.0.1\Common
           \SSIS\NewItem.xls\"" /set \package.variables[CreatedBy].Value;
           "\"Chirag\"" /set \package.variables[ContractDbConnectionString].Value;
           "\"Data Source=myserver\SQL2K5;User ID=sa;Password=sapass;
           Initial Catalog=Items;Provider=SQLNCLI.1;Persist Security Info=True;
           Auto Translate=False;\"" /set \package.variables[BatchID].Value;"\"1\"" 
           /set \package.variables[SupplierID].Value;"\"22334\""'

----now making "dtexec" SQL from dynamic values
set @ssisstr = 'dtexec /sq ' + @packagename + ' /ser ' + @servername + ' '
set @ssisstr = @ssisstr + @params
-----print line for verification 
--print @ssisstr

----
----now execute dynamic SQL by using EXEC. 
DECLARE @returncode int
EXEC @returncode = xp_cmdshell @ssisstr
select @returncode

现在我们将看到“dtexec”命令的变量传递结构

/SET \package\DataFlowTask.Variables[User::MyVariable].Value;newValue

现在，@returncode 变量将由“dtexec”命令返回，它将是两个记录集：第一个将返回以下可能值之一的代码，用于指示 SSIS 包的状态；第二个表将描述 SSIS 包执行期间发生的所有过程。

值	描述
0	包已成功执行。
1	包失败。
3	包被用户取消。
4	实用程序未能找到请求的包。找不到该包。
5	实用程序未能加载请求的包。无法加载该包。
6	实用程序遇到了语法或语义错误的内部错误，错误发生在命令行。

因此，通过这种方式，我们可以使用“xp_cmdshell”和“dtexec”命令从 SQL Server 的存储过程中调用 SSIS 包。并且我们永远不会遇到从 Web 服务调用 SSIS 时可能遇到的问题。

资源

“xp_cmdshell”和“dtexec”还可以用于更多功能。以下是这两个命令的链接，它们将详细描述它们的语法和用法

• dtexec: http://msdn.microsoft.com/en-us/library/ms162810.aspx
• xp_cmdshell: http://www.databasejournal.com/features/mssql/article.php/3372131

历史

• 2008 年 6 月 21 日：首次发布