Share via

ADO NET Destination Editor (Connection Manager Page)

  • Article

Use the Connection Manager page of the ADO NET Destination Editor dialog box to select the ADO.NET connection for the destination. This page also lets you select a table or view from the database.

To learn more about the ADO NET destination, see ADO NET Destination.

To open the Connection Manager page

  1. In SQL Server Data Tools (SSDT), open the Integration Services package that has the ADO NET destination.

  2. On the Data Flow tab, double-click the ADO NET destination.

  3. In the ADO NET Destination Editor, click Connection Manager.

Static Options

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.

Use a table or view
Select an existing table or view from the list, or create a new table by clicking New..

New
Create a new table or view by using the Create Table dialog box.

Note

When you click New, Integration Services generates a default CREATE TABLE statement based on the connected data source. This default CREATE TABLE statement will not include the FILESTREAM attribute even if the source table includes a column with the FILESTREAM attribute declared. To run an Integration Services component with the FILESTREAM attribute, first implement FILESTREAM storage on the destination database. Then, add the FILESTREAM attribute to the CREATE TABLE statement in the Create Table dialog box. For more information, see Binary Large Object (Blob) Data (SQL Server).

Preview
Preview results by using the Preview Query Results dialog box. Preview can display up to 200 rows.

Use bulk insert when available
Specify whether to use the SqlBulkCopy interface to improve the performance of bulk insert operations.

Only ADO.NET providers that return a SqlConnection object support the use of the SqlBulkCopy interface. The .NET Data Provider for SQL Server (SqlClient) returns a SqlConnection object, and a custom provider may return a SqlConnection object.

You can use the .NET Data Provider for SQL Server (SqlClient) to connect to MicrosoftAzure SQL Database.

If you select Use bulk insert when available, and set the Error option to Redirect the row, the batch of data that the destination redirects to the error output may include good rows.For more information about handling errors in bulk operations, see Error Handling in Data. For more information about the Error option, see ADO NET Destination Editor (Error Output Page).

Note

If a SQL Server or Sybase source table includes an identity column, you must use Execute SQL tasks to run a SET IDENTITY_INSERT statement before and after the ADO NET destination. The identity column property specifies an incremental value for the column. The SET IDENTITY_INSERT statement enables explicit values to be inserted into the identity column. To run the CREATE TABLE and SET IDENTITY statements on the same database connection, set the RetainSameConnection property of the ADO.NET connection manager to True. Also, use the same ADO.NET connection manager for the Execute SQL tasks and the ADO NET destination.

For more information, see SET IDENTITY_INSERT (Transact-SQL) and IDENTITY (Property) (Transact-SQL).

External Resources

Technical article, Loading data to Azure SQL Database the fast way, on sqlcat.com

See Also

ADO NET Destination Editor (Mappings Page)
ADO NET Destination Editor (Error Output Page)
ADO.NET Connection Manager
Execute SQL Task