OLE DB Source Adapters - ( SSIS Data Flow )

OLE DB Source adapter is used in SQL Server Integration Services (SSIS) to extract data from OLE DB databases such as Oracle, Access, and SQL Server. It allows the creation of a connection manager to access the data and specify the query to retrieve the data.

Syntax

The syntax for OLE DB Source Adapter is as follows:

SELECT column_name1, column_name2, column_name3
FROM table_name
WHERE condition;

Example

Let's consider an example to understand how OLE DB Source Adapter works:

Suppose we have a database named "company" and a table named "employee" where the employee id, name, and salary is stored. We need to extract data from the employee table using the OLE DB Source Adapter.

The query to retrieve the employee data would be:

SELECT employee_id, employee_name, employee_salary
FROM employee
WHERE employee_salary > 50000;

Output

The output of the OLE DB Source Adapter will be all the rows and columns that match the specified query.

Explanation

In SSIS, the OLE DB Source Adapter is used in the Data Flow Task to extract data from OLE DB databases. The adapter allows the creation of a connection manager that specifies the database details and credentials to access the database. You can then specify a query to retrieve the required data.

The OLE DB Source Adapter also allows the mapping of the source columns to destination columns using the data conversion and transformation tools included in SSIS.

Use

The OLE DB Source Adapter is used mainly to extract data from OLE DB databases for various ETL (Extract, Transform, Load) processes. It is used to retrieve the data and send it to the next stage in the data flow, either for transformation or loading into a destination database.

Important Points

• You must have the appropriate permissions to access the database from where you want to extract data.
• The query syntax should follow the rules set by the specific database management system you are using.

Summary

To sum up, OLE DB Source Adapter is a necessary tool when extracting data from OLE DB databases in SSIS. It allows the creation of a connection manager and specifying a query to retrieve data. It is an essential tool in various ETL processes and can map source columns to destination columns.