如何在SQL Server存储过程中执行DTS包
2024-08-31 00:48:18
供稿：网友
本文来源于网页设计爱好者web开发社区http://www.html.org.cn收集整理，欢迎访问。数据转换服务（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系统存储过程来实现。
