ADO NET Source
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 Microsoft Azure SQL Database. Connecting to SQL Database by using OLE DB is not supported. For more information about SQL Database, see General Guidelines and Limitations (Azure SQL Database).
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 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.
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 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.
ADO NET Source Troubleshooting
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.
ADO NET Source Configuration
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 AdventureWorks2012 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 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.
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 Set the Properties of a Data Flow Component.