在SQL Server存储过程中执行DTS包

数据转换服务(DTS)在数据库管理和研发的多种领域都有会涉及DTS：

数据仓库-将数据从原始的处理系统和表格中提取出来以供报表使用
建立OLAP
将大量数据从文本文档或其他非数据库格式的文档中拷贝到数据库
生成Microsoft Office文档报表
使用 Distributed Transaction Coordinator (DTC)实现多数据库操作
在客户的桌面程式或网站上，经常需要允许用户按需执行DTS包。在这种情况下，在部署DTS包时，您应该决定将DTS包安置在何处，连同通过何种手段调用他。

您的选择
要建立一个按需执行的DTS包，能够有多种选择。下面就对这些选择进行逐一说明。

SQL Server job
您能够在SQL Server里建立一个job，并调用sp_start_job存储过程。使用sp_start_job的不足之处在于他是个异步过程。由于他不能返回成功或失败指示，您必须强制使用sp_help_job系统存储过程查询job的结果。除非不关心job调用后的结果，否则异步的job将使桌面程式或Web程式变得很复杂。一个job能够被配置成非管理员（sa）模式，但需要一些额外的步骤。

在客户端桌面使用DTS DLLs
第二种方法是用户电脑载入Enterprise Manager或DTS DLLs，在用户的电脑上调用DTS包。虽然用户电脑执行DTS包有一定可行性，但也有不足：必须考虑到升级DTS包带来的分发和安装问题。

在服务器上使用sp_OA 扩展存储过程
第三种选择，也就是本文所介绍的核心内容，就是使用sp_OA系统存储过程族并有计划的调用DTS包。这种方案能够有效的避免上两种方案的弊端。

使用 VBScript调用DTS包
实现一个能够运行DTS包的存储过程的第一步是，编写一段VBScript代码。因为sp_OA存储过程使用起来有些麻烦，因此在利用sp_OA存储过程实现目标之前，要用VBScript编写您希望实现的代码。一般倾向于使用Visual Basic进行简单的脚本研发工作。如图A所示，通过在项目引用窗口中加入DTS包对象库，就能够在脚本中引用DTS包对象了。

图A：DTS对象库
在代码中使用了LoadFromStorageFile函数。一般说，研发工作应该在一个测试环境进行。了解DTS格式的结构化，对将测试产品变为实际产品时很有帮助。

Sp_OA 实现
写好了VBScript代码，就可用sp_OA扩展存储过程实现代码。和VBScript类似，sp_OA系统存储过程允许和对象库的COM API进行交互。

Sp_OACreate和在VB或VBScript中调用的CreateObject函数类似。Sp_OAGetProperty、sp_OASetProperty连同sp_OAMethod用来连接对象库中的特性和函数。和VB或 VBScript不同的是，sp_OA存储过程导致的COM 错误不会令SQL语句失败，因此必须手动检查每个使用sp_OA的函数是否工作正常。

同时，很多sp_OA存储过程都会引用参数，因此必须在sp_OA存储过程中的适当参数后加入OUTPUT语句。假如省略了OUTPUT 语句，T-SQL也不会发出警告信息。因此在运行时状态，虽然存储过程运行正常但也不会返回正确值。列表B是个周详的实现代码。

解决方案中包括能够重命名DTS包的表格连同实现的过程。其中sp_AdRunDTSPackageOnServer存储过程接收一个ID参数。在继续执行前，程式会从T_AdDTSPackageSetup表中，查找到达DTS包的SQL Server路径。

安全性
周详的安全性问题不在本文的讨论范围，这里要说的是一些必须考虑到的基本问题：

在主数据库的sp_OA扩展系统存储过程中，实现sp_AdRunDTSPackageOnServer存储过程需要EXECUTE权限。为了防止一些恶意用户通过sp_OA过程实现某种目的，能够针对应用程式修改SQL Server规则，以加强安全性。

T-SQL的CURRENT_USER函数对系统安全会有稍许帮助。使用CURRENT_USER和T_AdDTSPackageSetup表格中的区域，能够查询某个用户是否被设为：使用给定的DTS包。

DTS包在SQL Server上执行时，会受到SQL Server Agent服务的帐户配置影响，假如从文档系统中读取ASCII文档，应该确定SQL Server Agent的帐户配置对该文档有通过许可。
扩展范例
能够使用sp_OA系统存储过程和其他COM 库进行交互。同时在其他使用ODBC和ActiveX数据对象（ADO）的非SQL Server系统上，sp_OA也能够有效的调用存储过程。一个仅10行左右的VBScript脚本根本没有实用价值，而最后合成的T-SQL代码会变得很冗长。网上的SQL Server 2000 Books 包括周详的COM 对象库的支持说明，并包含了sp_OA系统存储过程的相关文档。当用户再需要按需运行DTS包时，不妨考虑使用sp_OA系统存储过程来实现。

标签：

版权申明：本站文章部分自网络，如有侵权，请联系：west999com@outlook.com
特别注意：本站所有转载文章言论不代表本站观点，本站所提供的摄影照片，插画，设计作品，如需使用，请与原作者联系，版权归原作者所有