OLE DB 目标OLE DB Destination

适用于:Applies to: 是SQL ServerSQL Server(所有支持的版本)yesSQL ServerSQL Server (all supported versions) 是 Azure 数据工厂中的 SSIS Integration RuntimeSSIS Integration Runtime in Azure Data Factoryyes Azure 数据工厂中的 SSIS Integration RuntimeSSIS Integration Runtime in Azure Data Factory适用于:Applies to: 是SQL ServerSQL Server(所有支持的版本)yesSQL ServerSQL Server (all supported versions) 是 Azure 数据工厂中的 SSIS Integration RuntimeSSIS Integration Runtime in Azure Data Factoryyes Azure 数据工厂中的 SSIS Integration RuntimeSSIS Integration Runtime in Azure Data Factory

OLE DB 目标用数据库表或视图或者用 SQL 命令,将数据加载到各种符合 OLE DB 的数据库中。The OLE DB destination loads data into a variety of OLE DB-compliant databases using a database table or view or an SQL command. 例如,OLE DB 源可以将数据加载到 MicrosoftMicrosoft Office Access 和 SQL ServerSQL Server 数据库的表中。For example, the OLE DB source can load data into tables in MicrosoftMicrosoft Office Access and SQL ServerSQL Server databases.

备注

如果数据源是 MicrosoftMicrosoft Office Excel 2007,则数据源需要一个不同于早期版本 Excel 的连接管理器。If the data source is MicrosoftMicrosoft Office Excel 2007, the data source requires a different connection manager than earlier versions of Excel. 有关详细信息,请参阅 连接到 Excel 工作簿For more information, see Connect to an Excel Workbook.

OLE DB 目标为数据加载提供了五种数据访问模式:The OLE DB destination provides five different data access modes for loading data:

  • 表或视图。A table or view. 可以指定现有的表或视图,也可以创建新表。You can specify an existing table or view, or you create a new table.

  • 使用快速加载选项的表或视图。A table or view using fast-load options. 可以指定现有的表或者创建新表。You can specify an existing table or create a new table.

  • 变量中指定的表或视图。A table or view specified in a variable.

  • 使用快速加载选项在变量中指定的表或视图。A table or view specified in a variable using fast-load options.

  • SQL 语句的运行结果。The results of an SQL statement.

备注

OLE DB 目标不支持参数。The OLE DB destination does not support parameters. 如果需要执行参数化 INSERT 语句,请考虑使用 OLE DB 命令转换。If you need to execute a parameterized INSERT statement, consider the OLE DB Command transformation. 有关详细信息,请参阅 OLE DB Command TransformationFor more information, see OLE DB Command Transformation.

当 OLE DB 目标加载使用双字节字符集 (DBCS) 的数据时,如果没有使用快速加载选项的数据访问模式,并且 OLE DB 连接管理器使用 MicrosoftMicrosoft OLE DB Provider for SQL ServerSQL Server (SQLOLEDB),则该数据可能会被损坏。When the OLE DB destination loads data that uses a double-byte character set (DBCS), the data may be corrupted if the data access mode does not use the fast load option and if the OLE DB connection manager uses the MicrosoftMicrosoft OLE DB Provider for SQL ServerSQL Server (SQLOLEDB). 为了确保 DBCS 数据的完整性,应将 OLE DB 连接管理器配置为使用 SQL ServerSQL Server Native Client 或使用以下任一快速加载访问模式:“表或视图 - 快速加载” 或“表名称或视图名称变量 - 快速加载” 。To ensure the integrity of DBCS data you should configure the OLE DB connection manager to use the SQL ServerSQL Server Native Client, or use one of the fast-load access modes: Table or view - fast load or Table name or view name variable - fast load. 这两个选项都可以在 “OLE DB 目标编辑器” 对话框中使用。Both options are available from the OLE DB Destination Editor dialog box. 编程 SSISSSIS 对象模型时,应将 AccessMode 属性设置为 OpenRowset Using FastLoadOpenRowset Using FastLoad From VariableWhen programming the SSISSSIS object model, you should set the AccessMode property to OpenRowset Using FastLoad, or OpenRowset Using FastLoad From Variable.

备注

如果用 设计器中的 “OLE DB 目标编辑器” SSISSSIS 对话框创建 OLE DB 目标要向其插入数据的目标表,可能需要手动选择新创建的表。If you use the OLE DB Destination Editor dialog box in SSISSSIS Designer to create the destination table into which the OLE DB destination inserts data, you may have to select the newly created table manually. 当 OLE DB 访问接口(如 OLE DB Provider for DB2)自动将架构标识符添加到表名称时,需要进行手动选择。The need for manual selection occurs when an OLE DB provider, such as the OLE DB provider for DB2, automatically adds schema identifiers to the table name.

备注

“OLE DB 目标编辑器” 对话框生成的 CREATE TABLE 语句可能需要修改,具体取决于目标类型。The CREATE TABLE statement that the OLE DB Destination Editor dialog box generates may require modification depending on the destination type. 例如,某些目标不支持 CREATE TABLE 语句所使用的数据类型。For example, some destinations do not support the data types that the CREATE TABLE statement uses.

此目标使用 OLE DB 连接管理器连接数据源,该连接管理器指定要使用的 OLE DB 访问接口。This destination uses an OLE DB connection manager to connect to a data source and the connection manager specifies the OLE DB provider to use. 有关详细信息,请参阅 OLE DB Connection ManagerFor more information, see OLE DB Connection Manager.

Integration ServicesIntegration Services 项目还提供了可从中创建 OLE DB 连接管理器的数据源对象,使数据源和数据源视图可用于 OLE DB 目标。An Integration ServicesIntegration Services project also provides the data source object from which you can create an OLE DB connection manager, to make data sources and data source views available to the OLE DB destination.

OLE DB 目标包括输入列和目标数据源中的列之间的映射。An OLE DB destination includes mappings between input columns and columns in the destination data source. 您不必将输入列映射到所有目标列,但有时如果没有将输入列映射到目标列可能会出错,具体取决于目标列的属性。You do not have to map input columns to all destination columns, but depending on the properties of the destination columns, errors can occur if no input columns are mapped to the destination columns. 例如,如果目标列不允许出现 Null 值,则必须将输入列映射到该列。For example, if a destination column does not allow null values, an input column must be mapped to that column. 另外,映射的列的数据类型必须是兼容的。In addition, the data types of mapped columns must be compatible. 例如,不能将数据类型为字符串的输入列映射到数据类型为数值的目标列。For example, you cannot map an input column with a string data type to a destination column with a numeric data type.

OLE DB 目标具有一个常规输入和一个错误输出。The OLE DB destination has one regular input and one error output.

有关数据类型的详细信息,请参阅 Integration Services Data TypesFor more information about data types, see Integration Services Data Types.

快速加载选项Fast Load Options

如果 OLE DB 目标使用快速加载数据访问模式,则可以在用户界面“OLE DB 目标编辑器” 中为目标指定以下快速加载选项:If the OLE DB destination uses a fast-load data access mode, you can specify the following fast load options in the user interface, OLE DB Destination Editor, for the destination:

  • 保持导入数据文件的标识值或使用由 SQL ServerSQL Server分配的唯一值。Keep identity values from the imported data file or use unique values assigned by SQL ServerSQL Server.

  • 在大容量加载操作过程中保留 Null 值。Retain a null value during the bulk load operation.

  • 在大容量导入操作过程中检查目标表或视图的约束。Check constraints on the target table or view during the bulk import operation.

  • 在大容量加载操作期间获取表级锁。Acquire a table-level lock for the duration of the bulk load operation.

  • 指定批中的行数和提交大小。Specify the number of rows in the batch and the commit size.

某些快速加载选项存储在 OLE DB 目标的特定属性中。Some fast load options are stored in specific properties of the OLE DB destination. 例如,FastLoadKeepIdentity 指定是否保持标识值,FastLoadKeepNulls 指定是否保持 Null 值,而 FastLoadMaxInsertCommitSize 则指定作为批提交的行数。For example, FastLoadKeepIdentity specifies whether to keep identify values, FastLoadKeepNulls specifies whether to keep null values, and FastLoadMaxInsertCommitSize specifies the number of rows to commit as a batch. 其他快速加载选项则存储在 FastLoadOptions 属性内的以逗号分隔的列表中。Other fast load options are stored in a comma-separated list in the FastLoadOptions property. 如果 OLE DB 目标使用存储于 FastLoadOptions 中和“OLE DB 目标编辑器” 对话框中列出的所有快速加载选项,则该属性的值将设置为 TABLOCK, CHECK_CONSTRAINTS, ROWS_PER_BATCH=1000If the OLE DB destination uses all the fast load options that are stored in FastLoadOptions and listed in the OLE DB Destination Editor dialog box, the value of the property is set to TABLOCK, CHECK_CONSTRAINTS, ROWS_PER_BATCH=1000. 值 1000 指示已将目标配置为使用 1000 行组成的批。The value 1000 indicates that the destination is configured to use batches of 1000 rows.

备注

目标中任何约束失败都将导致 FastLoadMaxInsertCommitSize 所定义的整批行失败。Any constraint failure at the destination causes the entire batch of rows defined by FastLoadMaxInsertCommitSize to fail.

除了在“OLE DB 目标编辑器” 对话框中公开的快速加载选项以外,还可以通过在“高级编辑器” 对话框的 FastLoadOptions 属性中键入选项,将 OLE DB 目标配置为使用以下大容量加载选项。In addition to the fast load options exposed in the OLE DB Destination Editor dialog box,you can configure the OLE DB destination to use the following bulk load options by typing the options in FastLoadOptions property in the Advanced Editor dialog box.

快速加载选项Fast load option 说明Description
KILOBYTES_PER_BATCHKILOBYTES_PER_BATCH 指定要插入的大小 (KB)。Specifies the size in kilobytes to insert. 选项的格式为 KILOBYTES_PER_BATCH = <positive integer value>。The option has the form KILOBYTES_PER_BATCH = <positive integer value>.
FIRE_TRIGGERSFIRE_TRIGGERS 指定是否在插入表上激发触发器。Specifies whether triggers fire on the insert table. 选项的格式为 FIRE_TRIGGERSThe option has the form FIRE_TRIGGERS. 出现该选项说明要激发触发器。The presence of the option indicates that triggers fire.
ORDERORDER 指定输入数据如何排序。Specifies how the input data is sorted. 选项格式为 ORDER <column name> ASC|DESC。The option has the form ORDER <column name> ASC|DESC. 可以列出任何列数,是否包括排序顺序是可选的。Any number of columns may be listed and it is optional to include the sort order. 如果省略排序顺序,则插入操作假定数据不排序。If sort order is omitted, the insert operation assumes the data is unsorted.

注意:如果使用 ORDER 选项根据表中的聚集索引对输入数据进行排序,可以提升性能。Note: Performance can be improved if you use the ORDER option to sort the input data according to the clustered index on the table.

Transact-SQLTransact-SQL 关键字传统上采用大写字母键入,但并不区分大小写。The Transact-SQLTransact-SQL keywords are traditionally typed using uppercase letters, but the keywords are not case sensitive.

若要了解快速加载选项的详细信息,请参阅BULK INSERT (Transact-SQL)To learn more about fast load options, see BULK INSERT (Transact-SQL).

OLE DB 目标故障排除Troubleshooting the OLE DB Destination

可以记录 OLE DB 目标对外部数据访问接口所做的调用。You can log the calls that the OLE DB destination makes to external data providers. 利用此日志记录功能,可以对 OLE DB 目标在执行将数据保存到外部数据源的操作进行故障排除。You can use this logging capability to troubleshoot the saving of data to external data sources that the OLE DB destination performs. 若要记录 OLE DB 目标对外部数据访问接口所做的调用,请在包级别启用包日志记录并选择 “诊断” 事件。To log the calls that the OLE DB destination makes to external data providers, enable package logging and select the Diagnostic event at the package level. 有关详细信息,请参阅 包执行的疑难解答工具For more information, see Troubleshooting Tools for Package Execution.

配置 OLE DB 目标Configuring the OLE DB Destination

可以通过 SSISSSIS 设计器或以编程方式来设置属性。You can set properties through SSISSSIS Designer or programmatically.

“高级编辑器” 对话框反映了可以通过编程方式进行设置的属性。The Advanced Editor dialog box reflects the properties that can be set programmatically. 有关可以在 “高级编辑器” 对话框中或以编程方式设置的属性的详细信息,请单击下列主题之一:For more information about the properties that you can set in the Advanced Editor dialog box or programmatically, click one of the following topics:

有关如何设置属性的详细信息,请单击下列主题之一:For more information about how to set properties, click one of the following topics:

OLE DB 目标编辑器(“连接管理器”页)OLE DB Destination Editor (Connection Manager Page)

使用 “OLE DB 目标编辑器” 对话框的 “连接管理器” 页可以为目标选择 OLE DB 连接。Use the Connection Manager page of the OLE DB Destination Editor dialog box to select the OLE DB connection for the destination. 使用此页还可以选择数据库中的表或视图。This page also lets you select a table or view from the database.

备注

如果数据源是 MicrosoftMicrosoft Office Excel 2007,则数据源需要一个不同于早期版本 Excel 的连接管理器。If the data source is MicrosoftMicrosoft Office Excel 2007, the data source requires a different connection manager than earlier versions of Excel. 有关详细信息,请参阅 连接到 Excel 工作簿For more information, see Connect to an Excel Workbook.

备注

OLE DB 目标的 CommandTimeout 属性未在 “OLE DB 目标编辑器” 中提供,但可以使用 “高级编辑器” 进行设置。The CommandTimeout property of the OLE DB destination is not available in the OLE DB Destination Editor, but can be set by using the Advanced Editor. 另外,某些快速加载选项仅在 “高级编辑器” 中提供。In addition, certain fast load options are available only in the Advanced Editor. 有关这些属性的详细信息,请参阅 OLE DB Custom Properties的“OLE DB 目标”部分。For more information on these properties, see the OLE DB Destination section of OLE DB Custom Properties.

仅当数据访问模式为“SQL 命令”时,CommandTimeout 属性才有效。The CommandTimeout property only takes effective when data access mode is SQL command.

静态选项Static Options

“无缓存”OLE DB connection manager
从列表中选择一个现有连接管理器,或通过单击“新建” 创建一个新连接。Select an existing connection manager from the list, or create a new connection by clicking New.

新建New
通过使用“配置 OLE DB 连接管理器” 对话框创建一个新连接管理器。Create a new connection manager by using the Configure OLE DB Connection Manager dialog box.

数据访问模式Data access mode
指定向目标中加载数据的方法。Specify the method for loading data into the destination. 加载双字节字符集 (DBCS) 数据需要使用一个快速加载选项。Loading double-byte character set (DBCS) data requires use of one of the fast load options. 有关针对大容量插入进行了优化的快速加载数据访问模式的详细信息,请参阅 OLE DB DestinationFor more information about the fast load data access modes, which are optimized for bulk inserts, see OLE DB Destination.

选项Option 说明Description
表或视图Table or view 将数据加载到 OLE DB 目标中的表或视图。Load data into a table or view in the OLE DB destination.
表或视图 - 快速加载Table or view - fast load 将数据加载到 OLE DB 目标中的表或视图,并使用快速加载选项。Load data into a table or view in the OLE DB destination and use the fast load option. 有关针对大容量插入进行了优化的快速加载数据访问模式的详细信息,请参阅 OLE DB DestinationFor more information about the fast load data access modes, which are optimized for bulk inserts, see OLE DB Destination.
表名变量或视图名变量Table name or view name variable 在变量中指定表或视图名称。Specify the table or view name in a variable.

相关信息在包中使用变量Related information: Use Variables in Packages
表名变量或视图名变量 - 快速加载Table name or view name variable - fast load 在变量中指定表或视图名称,并使用快速加载选项加载数据。Specify the table or view name in a variable, and use the fast load option to load the data. 有关针对大容量插入进行了优化的快速加载数据访问模式的详细信息,请参阅 OLE DB DestinationFor more information about the fast load data access modes, which are optimized for bulk inserts, see OLE DB Destination.
SQL 命令SQL command 使用 SQL 查询将数据加载到 OLE DB 目标中。Load data into the OLE DB destination by using a SQL query.

预览Preview
使用“预览查询结果” 对话框预览结果。Preview results by using the Preview Query Results dialog box. 预览最多可以显示 200 行。Preview can display up to 200 rows.

数据访问模式动态选项Data Access Mode Dynamic Options

每个 “数据访问模式” 设置都显示一组特定于该设置的动态选项。Each of the settings for Data access mode displays a dynamic set of options specific to that setting. 下面几节介绍对于每个 “数据访问模式” 设置均可用的所有动态选项。The following sections describe each of the dynamic options available for each Data access mode setting.

数据访问模式 = 表或视图Data access mode = Table or view

表或视图的名称Name of the table or the view
从数据源的可用表列表或视图列表中选择表或视图的名称。Select the name of the table or view from a list of those available in the data source.

新建New
通过使用“创建表” 对话框创建一个新表。Create a new table by using the Create Table dialog box.

备注

单击 “新建” 时, Integration ServicesIntegration Services 将基于所连接的数据源生成一条默认的 CREATE TABLE 语句。When you click New, Integration ServicesIntegration Services generates a default CREATE TABLE statement based on the connected data source. 即使源表包含一个已声明了 FILESTREAM 属性的列,此默认 CREATE TABLE 语句也不会包含 FILESTREAM 属性。This default CREATE TABLE statement will not include the FILESTREAM attribute even if the source table includes a column with the FILESTREAM attribute declared. 若要运行具有 FILESTREAM 属性的 Integration ServicesIntegration Services 组件,首先要在目标数据库上实现 FILESTREAM 存储。To run an Integration ServicesIntegration Services component with the FILESTREAM attribute, first implement FILESTREAM storage on the destination database. 然后在 “创建表” 对话框中将 FILESTREAM 属性添加到 CREATE TABLE 语句中。Then, add the FILESTREAM attribute to the CREATE TABLE statement in the Create Table dialog box. 有关详细信息,请参阅二进制大型对象 (Blob) 数据 (SQL Server)For more information, see Binary Large Object (Blob) Data (SQL Server).

数据访问模式 = 表或视图 – 快速加载Data access mode = Table or view - fast load

表或视图的名称Name of the table or view
使用此列表从数据库中选择表或视图,或单击“新建” 创建新表。Select a table or view from the database by using this list, or create a new table by clicking New.

新建New
通过使用“创建表” 对话框创建一个新表。Create a new table by using the Create Table dialog box.

备注

单击 “新建” 时, Integration ServicesIntegration Services 将基于所连接的数据源生成一条默认的 CREATE TABLE 语句。When you click New, Integration ServicesIntegration Services generates a default CREATE TABLE statement based on the connected data source. 即使源表包含一个已声明了 FILESTREAM 属性的列,此默认 CREATE TABLE 语句也不会包含 FILESTREAM 属性。This default CREATE TABLE statement will not include the FILESTREAM attribute even if the source table includes a column with the FILESTREAM attribute declared. 若要运行具有 FILESTREAM 属性的 Integration ServicesIntegration Services 组件,首先要在目标数据库上实现 FILESTREAM 存储。To run an Integration ServicesIntegration Services component with the FILESTREAM attribute, first implement FILESTREAM storage on the destination database. 然后在 “创建表” 对话框中将 FILESTREAM 属性添加到 CREATE TABLE 语句中。Then, add the FILESTREAM attribute to the CREATE TABLE statement in the Create Table dialog box. 有关详细信息,请参阅二进制大型对象 (Blob) 数据 (SQL Server)For more information, see Binary Large Object (Blob) Data (SQL Server).

保留标识Keep identity
指定加载数据时是否复制标识值。Specify whether to copy identity values when data is loaded. 仅在选择快速加载选项时,此属性才可用。This property is available only with the fast load option. 此属性的默认值为 falseThe default value of this property is false.

保留 NullKeep nulls
指定加载数据时是否复制 Null 值。Specify whether to copy null values when data is loaded. 仅在选择快速加载选项时,此属性才可用。This property is available only with the fast load option. 此属性的默认值为 falseThe default value of this property is false.

表锁Table lock
指定加载期间是否锁定表。Specify whether the table is locked during the load. 此属性的默认值为 trueThe default value of this property is true.

检查约束Check constraints
指定目标在加载数据时是否检查约束。Specify whether the destination checks constraints when it loads data. 此属性的默认值为 trueThe default value of this property is true.

每批行数Rows per batch
指定每批中的行数。Specify the number of rows in a batch. 此属性的默认值为 -1,表示尚未分配值。The default value of this property is -1, which indicates that no value has been assigned.

备注

如果在“OLE DB 目标编辑器” 中清空此文本框,则表示不希望为此属性分配自定义值。Clear the text box in the OLE DB Destination Editor to indicate that you do not want to assign a custom value for this property.

最大插入提交大小Maximum insert commit size
指定 OLE DB 目标在快速加载操作期间尝试提交的批大小。Specify the batch size that the OLE DB destination tries to commit during fast load operations. 值为 0 表示在处理完所有行之后以单批方式提交所有数据。The value of 0 indicates that all data is committed in a single batch after all rows have been processed.

备注

如果该 OLE DB 目标和其他数据流组件正在更新同一源表,则 0 值可能导致正在运行的包停止响应。A value of 0 might cause the running package to stop responding if the OLE DB destination and another data flow component are updating the same source table. 若要防止包停止,请将 “最大插入提交大小” 选项设置为 2147483647To prevent the package from stopping, set the Maximum insert commit size option to 2147483647.

如果为此属性提供一个值,目标将分批提交行,提交的行数是 (a) “最大插入提交大小” 与 (b) 当前正在处理的缓冲区中的剩余行数中的较小者。If you provide a value for this property, the destination commits rows in batches that are the smaller of (a) the Maximum insert commit size, or (b) the remaining rows in the buffer that is currently being processed.

备注

目标中任何约束失败都将导致“最大插入提交大小” 所定义的整批行失败。Any constraint failure at the destination causes the entire batch of rows defined by Maximum insert commit size to fail.

数据访问模式 = 表名变量或视图名变量Data access mode = Table name or view name variable

变量名称Variable name
选择包含表或视图名称的变量。Select the variable that contains the name of the table or view.

数据访问模式 = 表名变量或视图名变量 – 快速加载)Data Access Mode = Table name or view name variable - fast load)

变量名称Variable name
选择包含表或视图名称的变量。Select the variable that contains the name of the table or view.

新建New
通过使用“创建表” 对话框创建一个新表。Create a new table by using the Create Table dialog box.

备注

单击 “新建” 时, Integration ServicesIntegration Services 将基于所连接的数据源生成一条默认的 CREATE TABLE 语句。When you click New, Integration ServicesIntegration Services generates a default CREATE TABLE statement based on the connected data source. 即使源表包含一个已声明了 FILESTREAM 属性的列,此默认 CREATE TABLE 语句也不会包含 FILESTREAM 属性。This default CREATE TABLE statement will not include the FILESTREAM attribute even if the source table includes a column with the FILESTREAM attribute declared. 若要运行具有 FILESTREAM 属性的 Integration ServicesIntegration Services 组件,首先要在目标数据库上实现 FILESTREAM 存储。To run an Integration ServicesIntegration Services component with the FILESTREAM attribute, first implement FILESTREAM storage on the destination database. 然后在 “创建表” 对话框中将 FILESTREAM 属性添加到 CREATE TABLE 语句中。Then, add the FILESTREAM attribute to the CREATE TABLE statement in the Create Table dialog box. 有关详细信息,请参阅二进制大型对象 (Blob) 数据 (SQL Server)For more information, see Binary Large Object (Blob) Data (SQL Server).

保留标识Keep identity
指定加载数据时是否复制标识值。Specify whether to copy identity values when data is loaded. 仅在选择快速加载选项时,此属性才可用。This property is available only with the fast load option. 此属性的默认值为 falseThe default value of this property is false.

保留 NullKeep nulls
指定加载数据时是否复制 Null 值。Specify whether to copy null values when data is loaded. 仅在选择快速加载选项时,此属性才可用。This property is available only with the fast load option. 此属性的默认值为 falseThe default value of this property is false.

表锁Table lock
指定加载期间是否锁定表。Specify whether the table is locked during the load. 此属性的默认值为 falseThe default value of this property is false.

检查约束Check constraints
指定任务是否检查约束。Specify whether the task checks constraints. 此属性的默认值为 falseThe default value of this property is false.

每批行数Rows per batch
指定每批中的行数。Specify the number of rows in a batch. 此属性的默认值为 -1,表示尚未分配值。The default value of this property is -1, which indicates that no value has been assigned.

备注

如果在“OLE DB 目标编辑器” 中清空此文本框,则表示不希望为此属性分配自定义值。Clear the text box in the OLE DB Destination Editor to indicate that you do not want to assign a custom value for this property.

最大插入提交大小Maximum insert commit size
指定 OLE DB 目标在快速加载操作期间尝试提交的批大小。Specify the batch size that the OLE DB destination tries to commit during fast load operations. 默认值为 2147483647 ,表示在处理完所有行之后以单批方式提交所有数据。The default value of 2147483647 indicates that all data is committed in a single batch after all rows have been processed.

备注

如果该 OLE DB 目标和其他数据流组件正在更新同一源表,则 0 值可能导致正在运行的包停止响应。A value of 0 might cause the running package to stop responding if the OLE DB destination and another data flow component are updating the same source table. 若要防止包停止,请将 “最大插入提交大小” 选项设置为 2147483647To prevent the package from stopping, set the Maximum insert commit size option to 2147483647.

数据访问模式 = SQL 命令Data access mode = SQL command

SQL 命令文本SQL command text
输入 SQL 查询的文本,通过单击“生成查询” 来生成查询,或通过单击“浏览” 定位到包含查询文本的文件。Enter the text of a SQL query, build the query by clicking Build Query, or locate the file that contains the query text by clicking Browse.

备注

OLE DB 目标不支持参数。The OLE DB destination does not support parameters. 如果需要执行参数化 INSERT 语句,请考虑使用 OLE DB 命令转换。If you need to execute a parameterized INSERT statement, consider the OLE DB Command transformation. 有关详细信息,请参阅 OLE DB Command TransformationFor more information, see OLE DB Command Transformation.

生成查询Build query
使用“查询生成器” 对话框可直观地构造 SQL 查询。Use the Query Builder dialog box to construct the SQL query visually.

“浏览”Browse
使用“打开” 对话框可定位到包含 SQL 查询文本的文件。Use the Open dialog box to locate the file that contains the text of the SQL query.

分析查询Parse query
验证查询文本的语法。Verify the syntax of the query text.

OLE DB 目标编辑器(“映射”页)OLE DB Destination Editor (Mappings Page)

可以使用 “OLE DB 目标编辑器” 对话框的 “映射” 页,将输入列映射到目标列。Use the Mappings page of the OLE DB Destination Editor dialog box to map input columns to destination columns.

选项Options

可用输入列Available Input Columns
查看可用输入列的列表。View the list of available input columns. 使用拖放操作可以将表中的可用输入列映射到目标列。Use a drag-and-drop operation to map available input columns in the table to destination columns.

可用目标列Available Destination Columns
查看可用目标列的列表。View the list of available destination columns. 使用拖放操作可以将表中的可用目标列映射到输入列。Use a drag-and-drop operation to map available destination columns in the table to input columns.

输入列Input Column
查看选定的输入列。View the input columns that you selected. 可以通过选择 <ignore> 以从输出中排除列来移除映射。You can remove mappings by selecting <ignore> to exclude columns from the output.

目标列Destination Column
查看每个可用目标列,而不管是否已对其进行映射。View each available destination column, regardless of whether it is mapped or not.

OLE DB 目标编辑器(“错误输出”页)OLE DB Destination Editor (Error Output Page)

可以使用 “OLE DB 目标编辑器” 对话框的 “错误输出” 页指定错误处理选项。Use the Error Output page of the OLE DB Destination Editor dialog box to specify error handling options.

选项Options

输入/输出Input/Output
查看输入的名称。View the name of the input.

Column
未使用。Not used.

错误Error
指定发生错误时应执行的操作:忽略失败、重定向行或使组件失败。Specify what should happen when an error occurs: ignore the failure, redirect the row, or fail the component.

相关主题: 数据中的错误处理Related Topics: Error Handling in Data

截断Truncation
未使用。Not used.

说明Description
查看操作的说明。View the description of the operation.

将此值设置到选定的单元格Set this value to selected cells
指定发生错误或截断时应对所有选定单元格执行的操作:忽略失败、重定向行或使组件失败。Specify what should happen to all the selected cells when an error or truncation occurs: ignore the failure, redirect the row, or fail the component.

应用Apply
将错误处理选项应用到选定的单元格。Apply the error handling option to the selected cells.

OLE DB 源OLE DB Source

Integration Services (SSIS) 变量Integration Services (SSIS) Variables

数据流Data Flow