ADO NET Source
Applies to: 
SQL Server
SSIS Integration Runtime in Azure Data Factory
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 AdventureWorks2022 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 a 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 a 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.
ADO NET Source Editor (Connection Manager Page)
Use the Connection Manager page of the ADO NET Source Editor dialog box to select the ADO.NET connection manager for the source. This page also lets you select a table or view from the database.
To learn more about the ADO NET source, see ADO NET Source.
To open the Connection Manager page
In SQL Server Data Tools (SSDT), open the Integration Services package that has the ADO NET source.
On the Data Flow tab, double-click the ADO NET source.
In the ADO NET Source Editor, click Connection Manager.
Static Options
ADO.NET connection manager
Select an existing connection manager from the list, or create a new connection by clicking New.
New
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 | Retrieve data from a table or view in the ADO.NET data source. |
| SQL command | Retrieve data from the ADO.NET data source by using a SQL query. |
Preview
Preview results by using the Data View dialog box. Preview can display up to 200 rows.
Note
When you preview data, columns with a CLR user-defined type do not contain data. Instead the values <value too big to display> or System.Byte[] display. The former displays when the data source is accessed by using the ADO.NET provider, the latter when using the SQL 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.
Data access mode = SQL command
SQL command text
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
Use the Query Builder dialog box to construct the SQL query visually.
Browse
Use the Open dialog box to locate the file that contains the text of the SQL query.
ADO NET Source Editor (Columns Page)
Use the Columns page of the ADO NET Source Editor dialog box to map an output column to each external (source) column.
To learn more about the ADO NET source, see ADO NET Source.
To open the Columns page
In SQL Server Data Tools (SSDT), open the Integration Services package that has the ADO NET source.
On the Data Flow tab, double-click the ADO NET source.
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. The name provided will be displayed within the SSIS Designer.
ADO NET Source Editor (Error Output Page)
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.
To learn more about the ADO NET source, see ADO NET Source.
To open the Error Output page
In SQL Server Data Tools (SSDT), open the Integration Services package that has the ADO NET source.
On the Data Flow tab, double-click the ADO NET source.
In the ADO NET Source Editor, click Error Output.
Options
Input/Output
View the name of the data source.
Column
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.