Load Data by Using the ODBC Destination
This procedure shows how to load data by using the ODBC destination. To add and configure an ODBC destination, the package must already include at least one Data Flow task and source.
To load data using an ODBC destination
In SQL Server Data Tools, open the Integration Services package you want.
Click the Data Flow tab, and then, from the Toolbox, drag the ODBC destination to the design surface.
Drag an available output of a data flow component to the input of the ODBC destination.
Double-click the ODBC destination.
In the ODBC Destination Editor dialog box, on the Connection Manager page, select an existing ODBC connection manager or click New to create a new connection manager.
Select the data access method.
Table Name - Batch: Select this option to configure the ODBC destination to work in batch mode. When you select this option, you can set the Batch size.
Table Name - Row by Row: Select this option to configure the ODBC destination to insert each of the rows into the destination table one at a time. When you select this option, the data is loaded into the table one row at a time.
In the Name of the table or the view field, select an available table or view from the database from the list or type in a regular expression to identify the table.This list contains the first 1000 tables only. If your database contains more than 1000 tables, you can type the beginning of a table name or use the (*) wild card to enter any part of the name to display the table or tables you want to use.
You can click Preview to view up to 200 rows of the data from the table selected in the ODBC destination.
Click Mappings and then map columns from the Available Input Columns list to columns in the Available Destination Columns list by dragging columns from one list to another.
To configure the error output, click Error Output.
To save the updated package, click Save Selected Items on the File menu.