Extract Data by Using the ODBC Source
This procedure describes how to extract data by using an ODBC source. To add and configure an ODBC source, the package must already include at least one Data Flow task.
To extract data using an ODBC source
In SQL Server Data Tools, open the SQL Server 2017 Integration Services (SSIS) package you want.
Click the Data Flow tab, and then, from the Toolbox, drag the ODBC source to the design surface.
Double-click the ODBC source.
In the ODBC Source 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: Select a table or view in the database or type a regular expression to identify the table to which the ODBC connection manager connects.
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.
SQL Command: Type an SQL Command or click Browse to load the SQL query from a text file.
You can click Preview to view up to 200 rows of the data extracted by the ODBC source.
To update the mapping between external and output columns, click Columns and select different columns in the External Column list.
If necessary, update the names of output columns by editing values in the Output Column list.
To configure the error output, click Error Output.
To save the updated package, click Save Selected Items on the File menu.