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
In SQL Server Data Tools (SSDT), open the Integration Services package that has the ADO NET destination.
On the Data Flow tab, double-click the ADO NET destination.
In the ADO NET Destination Editor, click Connection Manager.
Select an existing connection manager from the list, or create a new connection by clicking 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..
Create a new table or view by using the Create Table dialog box.
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 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 Microsoft Azure 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).
If a SQL Server or Sybase source table includes an identity column, you must use Execute SQL tasks to enable IDENTITY_INSERT before the ADO NET destination and to disable it again afterward. (The identity column property specifies an incremental value for the column. The SET IDENTITY_INSERT statement lets explicit values from the source table be inserted into the identity column in the destination table.)
To run the SET IDENTITY_INSERT statements and the data loading successfully, you have to do the following things.
- Use the same ADO.NET connection manager for the Execute SQL tasks and for the ADO.NET destination.
- On the connection manager, set the RetainSameConnection property and the MultipleActiveResultSets property to True.
On the ADO.NET destination, set the UseBulkInsertWhenPossible property to False.
Technical article, Loading data to Windows Azure SQL Database the fast way, on sqlcat.com