Programming Samples

Click here to go to:



Excel VBA

Word VBA

MS Access

T-SQL

SSIS

SSRS

Power BI

Crystal Reports

SSAS

SQL Replication

C# Code

ASP .NET Code

Oracle PL/SQL

Database Diagramming


Back to Home Page


SSIS Lookup Data Flow Transformation

SSIS Lookups - Finding Exact Data Matches

This article describes how to create a Lookup Data Transformation Task in SSIS. The data sources for the project will be MS Access database tables for the Data Source and the Reference Data Source. The Tables contain the names of places in Hawaii. The names that are Exact Matches are to be located in the reference file. The output will sent to 2 Excel files: one with the Match data and one with the No Match data.

Lookup Data Transformation Background

As previously stated, an Excel data source with a listing of places in Hawaii Counties will be used. A 2nd data source, a Reference data source, from a table in an MS Access database will be used to match the data. The data will be processed through the Lookup task and sent it to another Excel file called Match.xls. The unmatched data will also be sent to a separate Excel file called NoMatch.xls.

Using an SSIS Lookup in a BIDS Project

Begin in Visual Studio with a Integrated Services Project.

VS 2008

Drag and drop a Data Flow Task on to the Control Flow tab. Click on Data Flow tab.

Data Flow Task

Lookup Connections

Right click in the Connection Managers tab and select an OLE DB Connection.

OLE DB Connection Manager

Select the database as the source. This project will use an MS Access database that has a table with the data to search through.

Database connection

Right click on the OLE DB Source and select Edit to map the source to the Connection.

Edit OLE DB

Select the OLE DB connection and the table to use.

OLE DB Source

Select the columns to use as output.

OLE DB Columns

Using the Lookup Task

Add a Lookup Data Transformation Task to the Data Flow tab. Right click on Edit to add the reference table and set up the columns.

Lookup Transformation

On the General section, select Redirect rows to no match output under "Specify how to handle rows with no matching entries". We will capture this information in an Excel file later.

Redirect Row not matching output

Set the connection for the reference table. This is the list that will be compared to the data source for matching names.

reference table

Click on the Available Input columns and drag to the available lookup columns to create a join between the 2 data sets.

Equi Join

Create a new connection for an Excel file to use for the matched data output. Right click in the Connection Managers area and select New Connection.

Connection Manager

Select Excel as the Connection Manager type and click Add.

SSIS Connection Manager

Browse to select the Excel file to use for the matched data and click OK.

Excel File Connection

Setting up Lookup Output

In the Data Flow task, drag an Excel Destination on to the tab and then drag the Green Arrow to this new Excel Destination. A pop up will appear prompting you to select the Input Output type: either Lookup Match Output or Lookup No Match Output. Select Lookup Match Output and click OK.

Lookup Match Output

The Excel Destination Editor will open. Select the OLE DB Connection manager of the Excel Match connection and select the worksheet to use. Click on Mappings to set the columns.

Excel Destination Editor

Drag the available Input columns to the Available Destination columns to join, if not already mapped.

Excel Join

Right click on the Green Arrow between the Lookup Task and the Lookup Match Output Excel Destination and select Data Viewers.

Data Viewers Selection

Click Add and select Grid. Click OK to accept the defaults. Note that there are other types of Data Viewers to choose from, if desired.

Data Viewer Grid

Click OK to add the Data Viewer to the Data Flow.

Data Viewer

To add an additional Excel Destination for the No Match Data, repeat the process for the Excel Match data, if desired, by adding another Excel connection and Excel Destination and dragging a 2nd green arrow to it for the No Match data.

Lookup Data Flow

Right click on the Package in the Solution Explorer and select Execute Package.

Exec Package

Execute Package

The Data Viewer will pop up showing the matched data.

Data Viewer Live

Open the Excel files after the process completes to view the matched data and the unmatched data.

Matched Data

Unmatched Data File

non Match Data

As you can see, the Lookup will only find Exact Matches. If the data is similar, it will still see it as a No Match.