OleDB Destination 用法

提示：扫一扫查出行
【扫一扫了解最新限行尾号】
复制提示

在SSIS工程的开发过程中，OLEDB 数据源和目标组件是最常用的数据流组件。从功能上讲，OLEDB 数据源组件用于从OLEDB 提供者（Provider）中获取数据，传递给下游组件，OLEDB提供者是外部数据源，SQL Server是其中一种OLEDB提供者；OLEDB目标组件用于把数据流插入到关系型数据表中。OLEDB数据源和目标组件非常灵活，支持动态传参，功能强大，本文总结了这两个组件经常被用到的属性和用法。

一，数据源组件

查看数据源组件的编辑器，对数据源组件的配置，主要分为三大部分：连接管理器（Connection Manager），数据列（Columns）和错误输出（Error Output）。

1，配置连接管理器

数据源组件的数据来源于外部数据源，SSIS从外部数据源中获取数据，并把数据以数据流（Data Stream）的格式加载到SSIS引擎中。在连接到外部数据源之前，必须配置连接管理器，需要指定指定OLEDB 连接管理器，指定：Provider ，SQL Server实例名称，身份验证和连接的数据库。在配置完成之后，可以点击“Test Connection”测试连接管理器是否配置成功。

在列表“OLEDB connection manager”中选择已经存在的连接管理器，或者点击按钮“New...”新建一个连接管理器，该连接管理器的作用域（Scope）是Package级别，只能用于当前Package；开发者可以通过在Project的顶层文件“Connection Managers”下，创建作用域为Project的连接管理器，当前Project的所有Package都能引用该连接管理器。

2，配置数据访问模式

数据访问模式是数据源组件获取数据的方式，SSIS引起共提供四种数据访问模式（Access Mode），分别是：

Table or View：从列表"Name of the table or the view"中选择获取数据的外部数据源（Table或View）。
Table name or view name variable ：从列表“Variable name”中选择变量，该变量的值是外部数据源（Table或View）的名称，在选择该模式时，必须把一个外部数据源（Table或View）的名称存放在变量中；
SQL command：在“SQL command text”输入TSQL命令，可以在TSQL脚本中使用参数，在TSQL命令中以？代表参数，需要创建参数映射；
SQL command from variable：将TSQL命令保存变量中，数据源组件从变量中获取TSQL命令；

二，参数映射

最常用的数据访问模式是SQL Command，这种模式有很大的灵活性和适用性，最根本的原因是该模式能够在命令中使用参数，动态改变参数的值，能够获取到不同的数据源，实现海量数据的增量更新。

示例：在OLEDB数据源组件中，参数不是使用@VariableName表示，而是使用 ? 代表一个参数，在SQL 命令中，每一个 ? 都代表一个参数：

由于 ? 在SQL命令中出现的顺序是固定的，因此可以通过序号0，1，2（序号从0开始）来定位到每一个参数，从而建立参数和变量之间的一一映射。

点击按钮“Parameters...”，打开“Set Query Parameters”窗体，把变量映射到SQL命令中的参数。

在中映射（Mappings）中，参数 ? 的序号和Parameters的名称是相同的，变量（Variables）的名称是在Package中创建变量（Variable），在Package执行时，SSIS引擎把变量的值传递给SQL命令。

变量在Variables窗体中创建，Name是变量的名称，Scope是变量的作用域，分为Scope和组件两个级别；DataType是变量的数据类型，这是CLR数据类型，在选择上，需要考虑CLR数据类型和TSQL 数据类型的映射；Value是变量的值。

三，数据源组件的外部列

外部列，也是数据源的输出列，点击左侧的“Columns”选项卡，能够编辑数据源的外部列，从“Available External Columns”中能够看到数据源所有可用的外部列，可以只输出部分外部列，但是，该外部列已经从外部数据源中获取，并传输到SSIS引擎中，对于无用的数据列，推荐从外部数据源中过滤，也就是从数据源组件的查询命令中过滤，这样，能够减少SSIS引擎的缓存消耗和网络带宽资源。

四，数据源组件的错误输出

数据源组件的外部列是外部数据源的原始数据列，而数据源组件在获取到外部数据之后，会向下游组件输出，该数据列是输出列，从外部列到输出列，有一个数据列的转换。而错误输出，是指在数据源组件出现转换错误时，配置数据列对错误的处理模式，数据列的转化，是指从外部列转化到数据源定义的输出列上，常用的转换错误是错误（Error）和截断（Truncation）。

从描述（Description）中可以看出，数据列的错误是指数据转化（Conversion）错误。

错误处理模式共有三种，分别是：

Ignore failure：忽略错误，是指数据源出现错误时，直接忽略错误，而使数据源组件正常运行下去；
Redirect row：重定向错误行，是指把错误的数据行重定向到另外一个数据目标组件中；也就是说，如果列值出现错误（Error或Truncation），那么SSIS引擎把该数据行重定向到数据源组件的错误输出（ErrorOutput）；
Fail component：组件错误，是指当错误时，数据源组件抛出异常，数据源组件停止运行，对已经导入到数据目标组件中的数据，不做改变。

五，数据源组件的高级编辑器

点击Edit，或直接双击数据源组件，弹出的是数据源编辑器，通过“Show Advanced Editor”按钮，弹出的是高级编辑器，能够编辑数据源组件的底层属性。

高级数据源组件，能够编辑数据源的：连接管理器，组件属性，列映射和输入/输出属性。

在Connection Managers中，显示数据源组件使用的连接管理器。

1，组件属性

在Component Properties中，能够修改数据源组件的底层属性，在通用属性（Common Properties）列表中，最重要的属性是：ValidateExternalMetadata，用于指定该组件是否在设计时（design-time）验证外部数据源的元数据（metadata），如果设置为false，SSIS引擎延迟到Package运行时（runtime）验证外部数据源的元数据。

用户属性（Custom Properties）和前文的提到的属性相同，不再赘述。

2，列映射

列映射是外部列和输出列的映射，和上文的第三节（三，数据源组件的外部列）相同，不再赘述。

3，输入和输出属性

点击分类“OLEDB Source Output”，从右边的“Common Properties”列表中查看数据源组件的输出属性，大多数属性是无法编辑的：

最重要的一个可编辑属性是：IsSorted，指定数据源是否已经排序，如果外部数据源通过 order by 子句排序，可以设置IsSorted属性为True。

通常情况下，关系型数据库的排序操作，比SSIS引擎的排序组件，执行性能更高，推荐在外部关系型数据库中执行排序，并输出已排序的数据。

外部列（External Columns）是外部数据源的数据列，点击外部列，能够编辑外部列的元数据，示例，点击外部列id，能够查看外部列的属性，并编辑外部列的数据类型。

输出列（Output Columns）是数据源组件向下游组件输出的数据列，在输出列和外部列之间存在元数据的转换，上文提到，转换分为两种类型：Error和Truncation，在输出列的通用属性（Common Properties）中，

转换属性：ErrorRowDisposition和TruncationRowDisposition用于指定在转换出现错误时的处理模式，这在上文第四节（四，数据源组件的错误输出）中已提到，不再赘述。

通用属性中，最重要的属性是：SortKeyPosition，默认值是0，表示该数据列不是排序列。正整数表示升序，负整数表示降序，排序列的序号，从1依次递增。

0：表示表列不是排序列
负整数：表示表列是按倒序排序，对应TSQL排序的 desc
正整数：表示表列是按升序排序，对应TSQL排序的 asc

如果外部数据源的IsSorted属性设置为true，那么请设置输出的排序列的排序键的位置，

例如，外部列按照 order by id asc,name desc排序，那么 id的 SortKeyPosition属性值是1，name的SortKeyPosition属性值是-2。

六，OLEDB 目标组件

目标组件的作用是把数据流加载到关系表中，目标组件在内部使用 insert 或bulk insert 命令把上游组件传递的数据插入到目标关系表中。目标组件共有5种数据访问模式，常用的是：“Table or view” 和 “Table or view - fast load”。

这两种数据访问模式的异同之处是：

“Table or view” 模式：目标组件在内部使用insert命令，把数据插入到目标关系表中。
“Table or view - fast load”模式：组件在内部使用bulk insert命令，把数据插入到目标关系表中；使用Fast load 选项，能够显著提高数据插入的性能。

1，设置fast load选项的属性

当选择fast load选择的数据访问模式时，目标组件内部使用bulk insert命令批量向目标关系表插入数据，目标组件的UI界面上额外增加以下属性，

这些属性代表的含义分别是：

Keep identity ：保持ID值，当目标表中存在标识列时，如果不勾选“Keep identity”，那么目标表的ID列插入失败，如果勾选“Keep identity”，那么目标表中的ID列和数据源保持相同。
Keep Nulls：如果目标表为某一列设置了default约束，当数据流传递null给该列时，正常情况下会触发目标表的default约束。如果不选中Keep Nulls，那么目标表使用default value来填充数据列；如果选中Keep Nulls，那么目标表保持该列为null。
Table Lock：如果勾选该选项，在数据加载期间，整个目标关系表加上表级锁。
Check Constraints：检查插入的数据是否符合目标关系表约束，如果事先能够确定插入的数据流符合目标关系表的约束，那么不勾选“Check Constraints”会显著提高数据插入的性能。

当插入大量数据的时候，适当控制 tempdb 和单个事务的大小，能够提高数据插入的的性能。

Rows per batch：在执行bulk insert时，配置每一个batch 插入的数据行的数量，默认值是-1，表示不指定数值，由SSIS引擎自主确定每个batch的数据行数量；
Maximum insert commit size：指定数据源组件提交一个事务时已经处理的数据行的数量，也就是说，当插入指定数量的数据行时，数据源组件提交一个事务，通过配置该选项，能够控制单个事务的大小；
- 如果设置属性值为0，指定在一个事务中完成插入所有的数据行，在一个事务中插入所有的数据行，可能导致事务日志过大；另外，如果数据源在导入期间被修改，目标组件会停止响应。
- 在插入一批数据行时，batch中的任意一条数据违反约束，数据库引擎将回滚整个事务，这意味着，整个batch的数据行插入操作是失败的。

2，目标组件的高级属性

目标组件也有高级编辑器，其输入和输出属性，分为外部列和输入列，外部列是目标组件接收的数据列，输入列是目标组件把数据插入到指定的关系表的数据列。如下图所示：