ADO NET Source
The ADO NET source consumes data from a .NET provider and makes the data available to the data flow.
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 AdventureWorks 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.
The ADO NET source uses an ADO.NET 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.
Data Type Support
The source converts any data type that does not map to a specific Integration Services data type to the DT_NTEXT Integration Services 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 Integration Services, the date data types, DT_DBDATE, DT_DBTIME2, DT_DBTIMESTAMP2, and DT_DBTIMESTAMPOFFSET, map to certain date data types in SQL Server. You can configure the ADO NET source to convert the date data types from those that SQL Server uses to those that Integration Services uses. To configure the ADO NET source to convert these date data types, set the Type System Version property of the ADO.NET connection manager to SQL Server 2008 or 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 ADO.NET connection manager, and then click Edit.)
Note
If the Type System Version property for the ADO.NET connection manager is set to SQL Server 2005, the system converts the SQL Server date data types to DT_WSTR.
The system converts user-defined data types (UDTs) to Integration Services binary large objects (BLOB) when the ADO.NET connection manager specifies the provider as the .NET Data Provider for SQL Server (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. For more information about column properties, see Modifying Column Properties.
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 Handling Errors in Data.
For more information about the Integration Services data types, supported data type conversions, and data type mapping across certain databases including SQL Server, see Integration Services Data Types.
For information about mapping Integration Services data types to managed data types, see Working with Data Types in the Data Flow.
Troubleshooting the ADO NET Source
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 Package Execution.
Configuring the ADO NET Source
You can set properties through SSIS 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 How to: Set the Properties of a Data Flow Component.
|
.gif "Integration Services icon (small)")
Stay Up to Date with Integration Services