Excel Source as Lookup Transformation Connection

Question

I need to have Lookup Transformation Task, where Connection will be a Excel Source. But SSIS allow to set only OLEDB Connection as Connection.

Is there any possibility to use Excel file as Connection for Lookup Transformation?

Answer 1

Merge sort is an option, but if you really want to use Excel as a source for the SSIS Lookup task, you can do it.

As you have correctly discerned, you cannot use the Excel Connection Manager in a Lookup task, it only accepts the OLE DB connection manager. The trick then, is to use an OLE DB connection manager with an Excel spreadsheet.

For this example, I have a spreadsheet with state codes and their full name and my source data only has the abbreviations flowing through. This walk through will wire up a lookup task against Excel to retrieve those values.

Keep these caveats in mind: Lookups are case sensitive, regardless of whether the source database (or file in this case) is case sensitive. Excel strings are always going to be interpreted as unicode/nvarchar.

Source Data

Given a simple file sitting at C:\tmp\LookupReference.xlsx that looks like the following

Connection Manager

We must first establish an OLE DB Connection Manager. Instead of the default "Native OLE DB\SQL Server Native Client 10.0" change that to "Native OLE DB\Microsoft Office 12.0 Access Database Engine OLE DB Provider". In the Server or file name, locate the source file. Clicking Test Connection at this point will result in an error.

Here comes the "magic." Click the All tab and scroll to the top. In Extended Properties, add the indicated string "Excel 12.0;HDR=YES; IMEX=1;" This tells the provider that we are going to use Excel 12.0 (.xlsx format) with a header row and the IMEX 1 tells the driver there will be intermixed data.

Your package sould now look something like this. A connection manager with extended properties set and assumes a preexisting data flow

Data Flow

To simplify matters, I have a script source that generates 3 rows of data with state codes MO, KS and NE and sends them down the pipeline. Your source will obviously be different but the concept will remain the same. Sample code provided in the annotation.

In your lookup transformation, you will need to write a query against the spreadsheet. It's similar to a normal database query except your table is going to be Sheet1$ unless you have a named range in which your table would be MyRange Note the $ is required when referencing a sheet. Using the sample spreadsheet above, my query would be

SELECT
    S.StateCode
,   S.StateName
FROM 
    `Sheet1$` S

I map my Script task column StateCode to the reference query's StateCode column and check the StateName field as I want to add that to my data flow.

I've put a Data viewer after the Lookup task to verify my lookups worked

Everything works and we're all happy.

Old Excel

If you are using a .xls file, you need to make the following changes. - In your Connection Manager, instead of the Office 12 provider, select the "Native OLE DB\Microsoft Jet 4.0 OLE DB Provider" - The Extended Properties become "EXCEL 8.0;HDR=Yes; IMEX=1;"

Minor Edit

Blogged this at Using Excel in an SSIS lookup transformation. You can also use a Cached Connection Manager to use any source for lookups in SSIS 2008+