ADO NET 源ADO NET Source

适用于: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

ADO NET 源使用来自 .NET 提供程序的数据,并使这些数据对数据流可用。The ADO NET source consumes data from a .NET provider and makes the data available to the data flow.

可使用 ADO NET 源连接到 MicrosoftMicrosoft Azure SQL 数据库Azure SQL DatabaseYou can use the ADO NET source to connect to MicrosoftMicrosoft Azure SQL 数据库Azure SQL Database. 不支持使用 OLE DB 连接到 SQL 数据库SQL DatabaseConnecting to SQL 数据库SQL Database by using OLE DB is not supported. 有关 SQL 数据库SQL Database 的详细信息,请参阅通用指导原则和限制(Azure SQL 数据库)For more information about SQL 数据库SQL Database, see General Guidelines and Limitations (Azure SQL Database).

数据类型支持Data Type Support

源会将未映射到特定 Integration ServicesIntegration Services 数据类型的任意数据类型转换为 DT_NTEXT Integration ServicesIntegration Services 数据类型。The source converts any data type that does not map to a specific Integration ServicesIntegration Services data type to the DT_NTEXT Integration ServicesIntegration Services data type. 即使数据类型为 System.Object,也会发生此转换。This conversion occurs even if the data type is System.Object.

可以将 DT_NTEXT 数据类型更改为 DT_WSTR 数据类型,也可以将 DT_WSTR 更改为 DT_NTEXT。You can change the DT_NTEXT data type to the DT_WSTR data type, or the change DT_WSTR to DT_NTEXT. 通过在 ADO NET 源的 “高级编辑器” 对话框中设置 DataType 属性可更改数据类型。You change data types by setting the DataType property in the Advanced Editor dialog box of the ADO NET source. 有关详细信息,请参阅 Common PropertiesFor more information, see Common Properties.

通过在 ADO NET 源之后使用数据转换,还可以将 DT_NTEXT 数据类型转换为 DT_BYTES 或 DT_STR 数据类型。The DT_NTEXT data type can also be converted to the DT_BYTES or DT_STR data type by using a Data Conversion transformation after the ADO NET source. 有关详细信息,请参阅 Data Conversion TransformationFor more information, see Data Conversion Transformation.

Integration ServicesIntegration Services中,日期数据类型 DT_DBDATE、DT_DBTIME2、DT_DBTIMESTAMP2 和 DT_DBTIMESTAMPOFFSET 映射到 SQL ServerSQL Server中的某些日期数据类型。In Integration ServicesIntegration Services, the date data types, DT_DBDATE, DT_DBTIME2, DT_DBTIMESTAMP2, and DT_DBTIMESTAMPOFFSET, map to certain date data types in SQL ServerSQL Server. 您可以配置 ADO NET 源,从而将日期数据类型从 SQL ServerSQL Server 使用的数据类型转换为 Integration ServicesIntegration Services 使用的数据类型。You can configure the ADO NET source to convert the date data types from those that SQL ServerSQL Server uses to those that Integration ServicesIntegration Services uses. 若要配置 ADO NET 源以便转换这些日期数据类型,请将 连接管理器的 Type System Version ADO.NETADO.NET 属性设置为 LatestTo configure the ADO NET source to convert these date data types, set the Type System Version property of the ADO.NETADO.NET connection manager to Latest. Type System Version 属性位于“连接管理器” 对话框的“全部” 页。(The Type System Version property is on the All page of the Connection Manager dialog box. 若要打开“连接管理器” 对话框,请右键单击 ADO.NETADO.NET 连接管理器,然后单击“编辑” 。To open the Connection Manager dialog box, right-click the ADO.NETADO.NET connection manager, and then click Edit.)

备注

如果将 连接管理器的 Type System Version ADO.NETADO.NET 属性设置为 SQL Server 2005,则系统会将 SQL ServerSQL Server 日期数据类型转换为 DT_WSTR。If the Type System Version property for the ADO.NETADO.NET connection manager is set to SQL Server 2005, the system converts the SQL ServerSQL Server date data types to DT_WSTR.

Integration ServicesIntegration Services 连接管理器将提供程序指定为 .NET Data Provider for ADO.NETADO.NET (SqlClient) 时,系统会将用户定义的数据类型 (UDT) 转换为 SQL ServerSQL Server 二进制大型对象 (BLOB)。The system converts user-defined data types (UDTs) to Integration ServicesIntegration Services binary large objects (BLOB) when the ADO.NETADO.NET connection manager specifies the provider as the .NET Data Provider for SQL ServerSQL Server (SqlClient). 当系统转换 UDT 数据类型时会应用下列规则:The system applies the following rules when it converts the UDT data type:

  • 如果数据是非大型 UDT,系统会将该数据转换为 DT_BYTES。If the data is a non-large UDT, the system converts the data to DT_BYTES.

  • 如果数据是非大型 UDT,并且数据库中列的 Length 属性设置为 -1 或大于 8,000 个字节的值,则系统会将该数据转换为 DT_IMAGE。If the data is a non-large UDT, and the Length property of the column on the database is set to -1 or a value greater than 8,000 bytes, the system converts the data to DT_IMAGE.

  • 如果数据是大型 UDT,系统会将该数据转换为 DT_IMAGE。If the data is a large UDT, the system converts the data to DT_IMAGE.

    备注

    如果 ADO NET 源未配置为使用错误输出,则系统会以 8,000 个字节为单位成块地使数据流向 DT_IMAGE 列。If the ADO NET source is not configured to use error output, the system streams the data to the DT_IMAGE column in chunks of 8,000 bytes. 如果 ADO NET 源配置为使用错误输出,则系统会将整个字节数组传递到 DT_IMAGE 列。If the ADO NET source is configured to use error output, the system passes the whole array of bytes to the DT_IMAGE column. 有关如何将组件配置为使用错误输出的详细信息,请参阅 数据中的错误处理For more information about how to configure components to use error output, see Error Handling in Data.

有关 Integration ServicesIntegration Services 数据类型、支持的数据类型转换以及某些数据库(包括 SQL ServerSQL Server)之间的数据类型映射的详细信息,请参阅 Integration Services 数据类型For more information about the Integration ServicesIntegration Services data types, supported data type conversions, and data type mapping across certain databases including SQL ServerSQL Server, see Integration Services Data Types.

有关将 Integration ServicesIntegration Services 数据类型映射为托管数据类型的详细信息,请参阅 在数据流中使用数据类型For information about mapping Integration ServicesIntegration Services data types to managed data types, see Working with Data Types in the Data Flow.

ADO NET 源故障排除ADO NET Source Troubleshooting

可以记录 ADO NET 源对外部数据访问接口所做的调用。You can log the calls that the ADO NET source makes to external data providers. 利用此日志记录功能,可以对 ADO NET 源执行的从外部数据源加载数据的操作进行故障排除。You can use this logging capability to troubleshoot the loading of data from external data sources that the ADO NET source performs. 若要记录 ADO NET 源对外部数据访问接口所做的调用,请在包级别启用包日志记录并选择 “诊断” 事件。To log the calls that the ADO NET source 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.

ADO NET 源配置ADO NET Source Configuration

通过提供定义结果集的 SQL 语句可以配置 ADO NET 源。You configure the ADO NET source by providing the SQL statement that defines the result set. 例如,连接到 AdventureWorks2012AdventureWorks2012 数据库并使用 SQL 语句 SELECT * FROM Production.Product 的 ADO NET 源可从 Production.Product 表提取所有行,并将数据集提供给下游组件。For example, an ADO NET source that connects to the AdventureWorks2012AdventureWorks2012 database and uses the SQL statement SELECT * FROM Production.Product extracts all the rows from the Production.Product table and provides the dataset to a downstream component.

备注

在您使用 SQL 语句调用从临时表返回结果的某一存储过程时,使用 WITH RESULT SETS 选项可为结果集定义元数据。When you use an SQL statement to invoke a stored procedure that returns results from a temporary table, use the WITH RESULT SETS option to define metadata for the result set.

备注

如果使用 SQL 语句来执行存储过程且包因为以下错误失败,则可以通过在 exec 语句前添加 SET FMTONLY OFF 语句来更正错误。If you use an SQL statement to execute a stored procedure and the package fails with the following error, you may be able to resolve the error by adding the SET FMTONLY OFF statement before the exec statement.

在数据源中找不到列 <列名>。Column <column_name> cannot be found at the datasource.

ADO NET 源使用 ADO.NETADO.NET 连接管理器连接到数据源,该连接管理器指定 .NET 提供程序。The ADO NET source uses an ADO.NETADO.NET connection manager to connect to a data source, and the connection manager specifies the .NET provider. 有关详细信息,请参阅 ADO.NET Connection ManagerFor more information, see ADO.NET Connection Manager.

ADO NET 源有一个常规输出和一个错误输出。The ADO NET source has one regular output and one error output.

可以通过 SSISSSIS 设计器或以编程方式来设置属性。You can set properties through SSISSSIS Designer or 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, see Set the Properties of a Data Flow Component.

ADO NET 源编辑器(“连接管理器”页)ADO NET Source Editor (Connection Manager Page)

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

若要了解有关 ADO NET 源的详细信息,请参阅 ADO NET SourceTo learn more about the ADO NET source, see ADO NET Source.

打开“连接管理器”页To open the Connection Manager page

  1. SQL Server Data Tools (SSDT)SQL Server Data Tools (SSDT)中,打开具有 ADO NET 源的 Integration ServicesIntegration Services 包。In SQL Server Data Tools (SSDT)SQL Server Data Tools (SSDT), open the Integration ServicesIntegration Services package that has the ADO NET source.

  2. 在“数据流” 选项卡上,双击 ADO NET 源。On the Data Flow tab, double-click the ADO NET source.

  3. “ADO NET 源编辑器” 中,单击 “连接管理器”In the ADO NET Source Editor, click Connection Manager.

静态选项Static Options

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

新建New
使用“配置 ADO.NET 连接管理器” 对话框创建新的连接管理器。Create a new connection manager by using the Configure ADO.NET Connection Manager dialog box.

数据访问模式Data access mode
指定从源选择数据的方法。Specify the method for selecting data from the source.

选项Option 说明Description
表或视图Table or view ADO.NETADO.NET 数据源中的表或视图中检索数据。Retrieve data from a table or view in the ADO.NETADO.NET data source.
SQL 命令SQL command 使用 SQL 查询从 ADO.NETADO.NET 数据源中检索数据。Retrieve data from the ADO.NETADO.NET data source by using a SQL query.

预览Preview
通过使用“数据视图” 对话框预览结果。Preview results by using the Data View dialog box. 预览版 最多可以显示 200 行。Preview can display up to 200 rows.

备注

预览数据时,数据类型为 CLR 用户定义类型的列不包含数据。When you preview data, columns with a CLR user-defined type do not contain data. 而是显示值 <value too big to display> 或 System.Byte[]。Instead the values <value too big to display> or System.Byte[] display. 使用 ADO.NETADO.NET 访问接口访问数据源时,显示前一个值;使用 SQL ServerSQL Server Native Client 访问接口访问数据源时,显示后一个值。The former displays when the data source is accessed by using the ADO.NETADO.NET provider, the latter when using the SQL ServerSQL Server Native Client provider.

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

数据访问模式 = 表或视图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.

数据访问模式 = 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.

生成查询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.

ADO NET 源编辑器(“列”页)ADO NET Source Editor (Columns Page)

可以使用“ADO NET 源编辑器” 对话框的“列” 页,将输出列映射到每个外部(源)列。Use the Columns page of the ADO NET Source Editor dialog box to map an output column to each external (source) column.

若要了解有关 ADO NET 源的详细信息,请参阅 ADO NET SourceTo learn more about the ADO NET source, see ADO NET Source.

打开“列”页To open the Columns page

  1. SQL Server Data Tools (SSDT)SQL Server Data Tools (SSDT)中,打开具有 ADO NET 源的 Integration ServicesIntegration Services 包。In SQL Server Data Tools (SSDT)SQL Server Data Tools (SSDT), open the Integration ServicesIntegration Services package that has the ADO NET source.

  2. 在“数据流” 选项卡上,双击 ADO NET 源。On the Data Flow tab, double-click the ADO NET source.

  3. “ADO NET 源编辑器” 中,单击 “列”In the ADO NET Source Editor, click Columns.

选项Options

可用外部列Available External Columns
查看数据源中可用外部列的列表。View the list of available external columns in the data source. 无法使用此表添加或删除列。You cannot use this table to add or delete columns.

“外部列”External Column
按照外部(源)列在您配置使用此源中数据的组件时的显示顺序,查看外部(源)列。View external (source) columns in the order in which you will see them when configuring components that consume data from this source.

输出列Output Column
为每个输出列提供唯一的名称。Provide a unique name for each output column. 默认值为所选外部(源)列的名称;不过,您也可以任选一个唯一的描述性名称。The default is the name of the selected external (source) column; however, you can choose any unique, descriptive name. 所提供的名称将显示在 SSISSSIS 设计器中。The name provided will be displayed within the SSISSSIS Designer.

ADO NET 源编辑器(“错误输出”页)ADO NET Source Editor (Error Output Page)

可以使用 “ADO NET 源编辑器” 对话框的 “错误输出” 页,选择错误处理选项以及设置错误输出列的属性。Use the Error Output page of the ADO NET Source Editor dialog box to select error handling options and to set properties on error output columns.

若要了解有关 ADO NET 源的详细信息,请参阅 ADO NET SourceTo learn more about the ADO NET source, see ADO NET Source.

打开“错误输出”页To open the Error Output page

  1. SQL Server Data Tools (SSDT)SQL Server Data Tools (SSDT)中,打开具有 ADO NET 源的 Integration ServicesIntegration Services 包。In SQL Server Data Tools (SSDT)SQL Server Data Tools (SSDT), open the Integration ServicesIntegration Services package that has the ADO NET source.

  2. 在“数据流” 选项卡上,双击 ADO NET 源。On the Data Flow tab, double-click the ADO NET source.

  3. “ADO NET 源编辑器” 中,单击 “错误输出”In the ADO NET Source Editor, click Error Output.

选项Options

输入/输出Input/Output
查看数据源的名称。View the name of the data source.

Column
查看在“ADO NET 源编辑器”对话框的“连接管理器”页上选择的外部(源)列 。View the external (source) columns that you selected on the Connection Manager page of the ADO NET Source Editor dialog box.

错误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
指定发生截断时应执行的操作:忽略失败、重定向行或使组件失败。Specify what should happen when a truncation occurs: ignore the failure, redirect the row, or fail the component.

说明Description
查看对错误的说明。View the description of the error.

将此值设置到选定的单元格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.

另请参阅See Also

DataReader 目标 DataReader Destination
ADO NET 目标 ADO NET Destination
数据流Data Flow