OLEDB数据源和目标组件

在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)中,

转换属性:ErrorRowDispositionTruncationRowDisposition用于指定在转换出现错误时的处理模式,这在上文第四节(四,数据源组件的错误输出)中已提到,不再赘述。

 

通用属性中,最重要的属性是: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,目标组件的高级属性

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

 当目标关系表的元数据和目标组件的输入列的元数据不一致时,需要手动同步,才能插入成功。

 
posted @ 2017-08-14 09:29  悦光阴  阅读(2238)  评论(0编辑  收藏  举报