| title | ADO NET Source | Microsoft Docs | |||||
|---|---|---|---|---|---|---|
| ms.custom | ||||||
| ms.date | 03/06/2017 | |||||
| ms.prod | sql-server-2014 | |||||
| ms.reviewer | ||||||
| ms.technology | integration-services | |||||
| ms.topic | conceptual | |||||
| f1_keywords |
|
|||||
| helpviewer_keywords |
|
|||||
| ms.assetid | 2a2f1750-2cda-4dda-9dca-623a96a6b3c0 | |||||
| author | janinezhang | |||||
| ms.author | janinez | |||||
| manager | craigg |
The ADO NET source consumes data from a .NET provider and makes the data available to the data flow.
You can use the ADO NET source to connect to [!INCLUDEmsCoName] [!INCLUDEssSDSfull]. Connecting to [!INCLUDEssSDS] by using OLE DB is not supported. For more information about [!INCLUDEssSDS], see General Guidelines and Limitations (Azure SQL Database).
The source converts any data type that does not map to a specific [!INCLUDEssISnoversion] data type to the DT_NTEXT [!INCLUDEssISnoversion] data type. This conversion occurs even if the data type is System.Object.
You can change the DT_NTEXT data type to the DT_WSTR data type, or the change DT_WSTR to DT_NTEXT. You change data types by setting the DataType property in the Advanced Editor dialog box of the ADO NET source. For more information, see Common Properties.
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. For more information, see Data Conversion Transformation.
In [!INCLUDEssISnoversion], the date data types, DT_DBDATE, DT_DBTIME2, DT_DBTIMESTAMP2, and DT_DBTIMESTAMPOFFSET, map to certain date data types in [!INCLUDEssNoVersion]. You can configure the ADO NET source to convert the date data types from those that [!INCLUDEssNoVersion] uses to those that [!INCLUDEssISnoversion] uses. To configure the ADO NET source to convert these date data types, set the Type System Version property of the [!INCLUDEvstecado] connection manager to Latest. (The Type System Version property is on the All page of the Connection Manager dialog box. To open the Connection Manager dialog box, right-click the [!INCLUDEvstecado] connection manager, and then click Edit.)
Note
If the Type System Version property for the [!INCLUDEvstecado] connection manager is set to SQL Server 2005, the system converts the [!INCLUDEssNoVersion] date data types to DT_WSTR.
The system converts user-defined data types (UDTs) to [!INCLUDEssISnoversion] binary large objects (BLOB) when the [!INCLUDEvstecado] connection manager specifies the provider as the .NET Data Provider for [!INCLUDEssNoVersion] (SqlClient). The system applies the following rules when it converts the UDT data type:
-
If the data is a non-large UDT, the system converts the data to DT_BYTES.
-
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.
-
If the data is a large UDT, the system converts the data to DT_IMAGE.
[!NOTE]
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. 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.
For more information about the [!INCLUDEssISnoversion] data types, supported data type conversions, and data type mapping across certain databases including [!INCLUDEssNoVersion], see Integration Services Data Types.
For information about mapping [!INCLUDEssISnoversion] data types to managed data types, see Working with Data Types in the Data Flow.
You can log the calls that the ADO NET source makes to external data providers. You can use this logging capability to troubleshoot the loading of data from external data sources that the ADO NET source performs. 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.
You configure the ADO NET source by providing the SQL statement that defines the result set. For example, an ADO NET source that connects to the [!INCLUDEssSampleDBUserInputNonLocal] 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.
Note
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.
Note
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.
The ADO NET source uses an [!INCLUDEvstecado] connection manager to connect to a data source, and the connection manager specifies the .NET provider. For more information, see ADO.NET Connection Manager.
The ADO NET source has one regular output and one error output.
You can set properties through [!INCLUDEssIS] 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.