在服务器端,经常需要调用一些服务包,例如通过创建Web服务来实现。但有时,希望传递一些Excel文件或平面文件到SSIS包中,而这些文件必须传输到服务器上才能在SSIS包中使用。在这种情况下,如果Web服务被限制使用服务器上的资源,可能会引发一些安全问题。因此,需要寻找其他方法来调用SSIS,而不是使用Web服务。
本文假设已经熟悉创建SSIS包,以及如何在包中添加变量和如何在代码中调用SSIS包。
本文附带了两个文件:
首先,将介绍除了使用“Web服务”之外的另一种调用SSIS包的方法。可以使用存储过程来调用SSIS包。怎么做呢?
在SQL Server2005中有一个系统存储过程叫做xp_cmdshell,默认情况下它是禁用的,需要手动启用这个SP才能使用。这可以通过两种方式实现:一种是运行一些脚本(在enablexp_cmdscript.sql文件中提供),另一种是使用“SQL Server表面区域配置”工具,该工具随SQL Server 2005一起安装。
xp_cmdshell是微软提供的扩展存储过程,存储在master数据库中。这个存储过程允许通过T-SQL代码直接向Windows命令行发出操作系统命令。如果需要,这些命令的输出将返回给调用例程。
从SQL Server安装程序的程序菜单中启动表面区域配置工具。它看起来像这样:
现在,点击“表面区域配置功能”链接,将看到以下屏幕。从左侧菜单中选择实例名称,并在下面点击xp_cmdshell选项,就像这样:
只需启用xp_cmdshell选项,重启SQL Server服务后,xp_cmdshell存储过程就会被启用。如果不想这样做,只需在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表中。
需要传递的变量有:
现在,将创建一个动态SQL脚本,这意味着可以使用它来调用任何SSIS包,只需要传递必要的变量。
DECLARE @ssisstr VARCHAR(8000), @packagename VARCHAR(200), @servername VARCHAR(100)
DECLARE @params VARCHAR(8000)
-- 包名称
SET @packagename = 'ImportItemFile'
-- 服务器名称
SET @servername = 'myserver\sql2k5'
-- 请将此行在单行中,已经将此行分成多行
-- 由于文章格式。
-- 在SSIS包中传递的包变量。
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"'
-- 现在制作"dtexec" SQL来自动态值
SET @ssisstr = 'dtexec /sq ' + @packagename + ' /ser ' + @servername + ' ' + @params
-- 打印行以供验证
PRINT @ssisstr
-- 现在通过使用EXEC执行动态SQL。
DECLARE @returncode INT
EXEC @returncode = xp_cmdshell @ssisstr
SELECT @returncode
/SET \package\DataFlowTask.Variables[User::MyVariable].Value;newValue
@returncode变量将由dtexec命令返回,它将返回两个记录集,第一个将返回以下可能值的代码,这将指示SSIS包的状态,第二个表将描述在执行SSIS包过程中发生的所有过程。
值 | 描述 |
---|---|
0 | 包执行成功。 |
1 | 包失败。 |
3 | 用户取消了包。 |
4 | 工具无法定位请求的包。包找不到。 |
5 | 工具无法加载请求的包。包无法加载。 |
6 | 工具遇到了命令行中的内部错误,或者是语法错误或语义错误。 |