OLE DB Source

The OLE DB source extracts data from a variety of OLE DB-compliant relational databases by using a database table, a view, or an SQL command. For example, the OLE DB source can extract data from tables in Microsoft Office Access or SQL Server databases. If the data source is Microsoft Office Excel 2007, the data source requires a different connection manager than earlier versions of Excel.

---

The OLE DB source provides four different data access modes for extracting data:

• A table or view.

• A table or view specified in a variable.

• The results of an SQL statement. The query can be a parameterized query.

• The results of an SQL statement stored in a variable.

The OLE DB source has one regular output and one error output.

Using Parameterized SQL Statements

The types of parameter names that are supported vary by provider. For example, some providers require that we use the variable or column names, whereas some providers require that we use symbolic names such as 0 or Param0. We should see the provider-specific documentation for information about the parameter names to use in SQL statements.

When we use an OLE DB connection manager, we cannot use parameterized subqueries, because the OLE DB source cannot derive parameter information through the OLE DB provider.

Specifying Parameters by Using Ordinal Positions

The order in which the parameters are listed in the Parameters list in the Set Query Parameter dialog box governs which parameter marker they are mapped to at run time. The first parameter in the list maps to the first ? in the SQL statement, the second to the second ?, and so on. For ex:

SELECT * FROM Production.Product WHERE Color = ? AND Size = ?

Specifying Parameters by Using Names

The parameter names must match the names that the stored procedure, run by the SELECT statement or the EXEC statement, expects. For ex:

EXEC uspGetWhereUsedProductID ?, ?

The stored procedure expects the variables, @StartProductID and @CheckDate, to provide parameter values. The order in which the parameters appear in the Mappings list is irrelevant. The only requirement is that the parameter names match the variable names in the stored procedure, including the @ sign.

Some important Custom properties specific to OLE DB Source

• AccessMode: The mode used to access the database. The possible values are Open Rowset, Open Rowset from Variable, SQL Command, and SQL Command from Variable. The default value is Open Rowset.

• OpenRowset: The name of the database object used to open a rowset.

• OpenRowsetVariable: The variable that contains the name of the database object used to open a rowset.

• ParameterMapping: The mapping from parameters in the SQL command to variables.

• SqlCommand: The SQL command to be executed.

• SqlCommandVariable: The variable that contains the SQL command to be executed.

The following example will copy data from Employee table into Employee_new table using OLE DB source.

Before moving ahead with example, refer earlier articles:

Create a new SSIS project using BIDS / SSDT

Data Flow Task

Assuming we are following these tutorials sequentially, let’s first TRUNCATE or DELETE data from Employee_new table.

Open SSISStudy /SSISStudy2012 project created earlier. Open Source Explorer and right click on SSIS Packages and select New SSIS Package. This should create new SSIS package. Rename it to OLEDBSourceExample.

Drag and drop Data Flow Task into Control Flow area. Open Data Flow Tab. Drag and drop OLE DB Source into Data Flow area.

Right click on OLE DB Source and select Edit to open OLE DB Source Editor. 

Click on New button to open Configure OLE DB Connection Manager window. Either select from the existing Data connections or click on New to create new data connection. 

Select the Provider, specify the Server, Authentication type (and credentials is sql server authentication) and database. Click on Test Connection to validate the data connection. 

Click OK till we return back to OLE DB Source Editor.

We can configure OLE DB Source to select data from table or view name directly, table or view name in variable, directly providing the SQL statement which could be parameterized or SQL statement in variable.

• Table name or View name

Select Data access mode as Table or view and under Name of the table or the view, select [HumanResources].[Employee]

• Table name or View name stored in variable

For this, let’s create a string variable TableOrViewName and specify value as [HumanResources].[Employee]. Right click on the Data Flow area and select Variables.

Click on Add Variable

Type Name as TableOrViewName, DataType as String and Value as [HumanResources].[Employee]

In OLE DB Source Editor, under Data access mode, select Table name or view name variable and select User::TableOrViewName under Variable name.

• SQL statement(with or without parameters)

In OLE DB Source Editor, under Data access mode, select SQL command. Type the SQL statement under SQL command text based on if the query is parameterized or not.

If no parameters, then type: SELECT * FROM HumanResources.Employee

If parameterized, then type: SELECT * FROM HumanResources.Employee WHERE EmployeeID = ?

The use of ? or @parameterName depends on the provider used. For this example, the provider is Native SQL and it supports use of ? as parameter in the query. One needs to check the provider documentation for more information.

Click on Parameters button on OLE DB Source Editor to open Set Query Parameters window. Here we map the query parameters with variables.

As per the query, we are required to pass EmployeeID value which is of type integer. Under Mappings and Variables, either select existing variables or select <New Variable...> to open Add Variable window. Set Name as EmployeeID, Value type as Int32 and Value as 100 and click OK.  

If the SQL statement passed is a stored procedure, repeat similar steps as discussed above. For ex, AdventureWorks database contains a stored procedure named dbo.uspGetEmployeeManagers which provides manager hierarchy information for given EmployeeID. Under SQL command text in OLE DB Source Editor, type

dbo.uspGetEmployeeManagers ?

Click Parameters button and under Parameters, replace Parameter0 with @EmployeeID. This is the parameter name in the stored procedure to which we are passing the value.

• SQL statement in variable

Create a string variable named SQLStmt with value as: SELECT * FROM HumanResources.Employee

Open OLE DB Source Editor. Under Data access mode, select SQL command from variable. Under Variable name, select User::SQLStmt. 

Drag and drop OLE DB Destination on the Data Flow area. Connect Source to Destination and configure destination to copy data from source to Employee_new table. Execute the package.

Click for NEXT article in the sequence.

 

Please feel free to correct me by commenting your suggestions and feedback.