SSIS高级转换任务—OLE DB命令

OLE DB Commond任务接受输入流中的行数据并执行一段SQL语句或自定义存储过程。这个转换和Execute Sql任务容易混淆，但是它非常类似于在ADO连接中循环接收一个结果集中的数据，创建，执行一个ADO命令。输入流中的参数数据可以提供给一个Sql命令或者存储过程。这里我们看到“循环”可能就会联想到另外一个词“性能”。这可能涉及到更新，插入，删除语句，每次都会有断开，连接的操作。但是并不意味着这个转换一无是处，这里我们着重理解这个任务如何配置和使用的。要注意有多少输入数据并在需要缓存数据到临时表或实际表时权衡性能和伸缩性，以达到最优效果。

在配置这个任务的时候，基本点要点是设置能够获得SQL语句的连接，提供可以执行的SQL语句，设置输入流中的数据和SQL语句参数的映射关系。打开编辑界面，这个任务的编辑界面有4个标签，并使用高级编辑：

1. 连接管理:设置OLE DB连接，通过这个连接获得要执行的SQL语句，要注意输入流中的参数数据并不一来自这个源
2. 组件属性:在这个标签内，SQLCommand属性设置SQL语句，CommandTimeOut属性设置连接失效时间，这点和ADO连接对象的工作原理是一样的，设置为0表示没有失效时间。在这个标签内还可以设置任务名字和描述
3. 列映射:这个标签显示输入流中的列和目标列，即SQL语句中的参数名字。使用鼠标拖拽的方法将他们连接起来。他们是一一对应的，假设语句中的参数要比输入流中的多，需要使用派生列转换产生多余的列
4. 输入和输出属性:多数时候在列映射标签内可以完成输出列设置，但是如果OLE DB数据源不支持派生参数，需要在这里使用ParametersNames和DBParamInfoFlags属性设置输出列

学习这个转换任务的最好方法是操作一个例子。这个例子假设要验证每天的存款数据并在数据库中创建存款项。您还必须为每个客户建立支付交易，以便会计核算人员通过软件来审查。在这个例子中我们不需要知道如何建立存款项和支付交易，我们只是建立两个空的存储过程来完成这项工作。

1. 创建一个package 命名为OLE DB Command，添加一个Data Flow tesk，双击进入Control Flow
2. 新建一个文本文件，并保存到c:\ole db eft data.txt，文件内容如下:

       CustomerID,DepositAmt,DepositDate,Invoice

       XY-111-222,$100.00,07/13/2005,222-063105

       XX-Z11-232,$1000.00,07/13/2005,232-063105

       XX-Y88-233,$555.00,07/13/2005,233-053105

3. 运行下面的SQL语句，在数据库AdventureWorks中创建两个空的存储过程

       USE ADVENTUREWORKS

       GO

       CREATE PROC usp_DepositTrans_Add (

            @CUSTOMERID varchar(10),

            @DEPOSITAMT money,

            @DEPOSITDATE smalldatetime,

            @INVOICE varchar(15))

       AS

     ---一个空的存储过程只是为了做实例

       GO

       CREATE PROC usp_PaymentTrans_Add (

            @CUSTOMERID varchar(10),

            @DEPOSITAMT money,

            @DEPOSITDATE smalldatetime,

            @INVOICE varchar(15))

       AS

     --一个空的存储过程只是为了做实例

4. 添加一个Flat File Source，连接到c:\ole db eft data.txt
5. 在Data Flow设计界面内拖放一个OLE DB Command task，将Flat File Source和它连接起来
6. 打开OLE DB Command的编辑界面，在Connection Manager设置连接到数据库AdventureWorks。在Component Properties标签界面内，设置SQLCommand属性为usp_DepositTrans_Add ?, ?, ?, ?。这里?表示一个输入参数。最后的界面如图1
   
   图1
7. 在Column Mapping标签内将输入列和对应的参数连接起来，如图2
   
   图2
8. 再添加一个OLE DB Command，将第一个和第二个连接起来，配置和第一个类似，但在这里SQL Commond属性设置为usp_PaymentTrans_Add ?, ?, ?, ?
   运行这个package，文本文件中的三行数据作为这两个存储过程的参数输入执行。如果存储过程是真实存在的，就会创建三个存款项和三个交易。这里我们也可以看到这里可以重复使用这三个存储过程，只要提供适当的参数。在这个例子中，可以重复使用现有的逻辑，将一批数据一次输入到package中执行。