What is Lookup Transformation?

Using columns that you designate from your Source Data Source, it draws a line to the Secondary Data Source and project columns.

Here is Microsoft’s own definition:

Link
“The Lookup transformation performs lookups by joining data in input columns with columns in a reference dataset. You use the lookup to access additional information in a related table that is based on values in common columns”.

Back End Database Platforms Supported

There are some restrictions, such as:

OLE DB Provider

The Only 3 providers that are supported are

SQL Server

Oracle

DB2

Join Type

It is an Equijoin, an inner join so to speak

Only Source Records that have corresponding entries in the Secondary Table are returned

On each Source Record that have more than one Secondary Record, only one matching record is returned

Background

Microsoft Business Intelligence Studio has a built in tooling to address such problems. Let us touch on one of them.

Set up Lab

Let us create our database objects.

We have a simple database structure with two tables; dbo.custList and dbo.custListDest. The only difference between the two tables is that the “inceptionDate” can be null in the source table, but not in the destination table.

Here is what our tables look like in SQL Server Management Studio.

Design Table – dbo.custList ( Source Table )

Design Table – dbo.custListDest ( Destination Table )

Data – dbo.custList ( Source Table )

Here is what our data looks like.

Notice that using CTRL-0 we intentionally nulled out some rows’ inceptionDate column.

Business Intelligence Studio (BIDS)

Control

Here is what our BIDS Task Flowchart looks like:

Task – Execute SQL Task

Our first task is to remove all records from the destination table, truncate table dbo.custListDest.

Task – Data Flow Task

Data Flow Task

Here is our Data Flow Task

Thank goodness the flow is straightforward. It starts off with our OLE DB Source, into our OLE DB Destination, and errors are logged into the Flat File Destination.

OLE DB Destination – Connection Manager

OLE DB Destination – Mappings

OLE DB Destination – Error Output

OLE DB Destination Editor – Settings

Tab

Element

Value

Connection Manager

Data Access Mode

Table or view fast load

Keep Identity

Yes

Keep nulls

Yes

Table lock

Off

Check Constraints

Yes

Rows per batch

Please use an optimal value based on what your environment can support

Mappings

Please map the columns based on your need

Error Output

Error

Redirect Error

Data Flow Path Editor

Pasted below is us passing off errors unto an OLEDB Destination Output.

Settings

Flat File Destination

Flat File Destination Editor – Connection Manager

Flat File Destination Editor – Flat File Connection Manager Editor

Within the FlatFile Destination Editor, we clicked on the Edit button to customize our Error File.

Flat File Destination Editor – Mappings

Here are the columns that we want to capture in our error file.

Settings

Tab

Element

Value

Connection Manager

Overwrite data in the file

Yes

File name

Please choose an existing folder in your user folder

Unicode

Yes ( to support internationalization )

Format

Delimited

Column names in the first data row

Yes

Profiling

SQL Server Profiler

As good developers we ran SQL Server profiler and took a quick look at the SQL Traffic. Seeing that “insert bulk” is in use, we feel comfortable about the throughput.