The Lookup Plus Component is an SSIS Data Flow Component providing enhanced functionality as compared to the standard SSIS Lookup component. To simplify your data flows, the component allows multiple lookup definitions in the same dialog and definition of values when the input cannot be matched or when it is undefined. In addition the Lookup Plus component supports a variety of connection types as a source, making it quite versatile.
by Fernando Sanchez on October 12, 2018
In this Quick Start article, we will show you step by step in How-To create a simple package that performs a lookup and set an active flag using the COZYROC’S Lookup Plus component and Derived Column Transformation to create a new column value. We will use the OLE DB Source component in the Data Flow to read the data from a local AdventureWorks2016CTP3 database and write it to a different table in another database using the OLE DB Destination component.
In order to complete this task you must have Microsoft SQL Server 2017 Enterprise Edition or Developer Edition, and your computer must have the following minimum requirements:
- Windows 10 version 1507 or higher: Home, Professional, Education, and Enterprise (LTSC and S are not supported)
- Windows Server 2016: Standard and Datacenter
- Windows 8.1 (with Update 2919355): Core, Professional, and Enterprise
- Windows Server 2012 R2 (with Update 2919355): Essentials, Standard, Datacenter
- Windows 7 SP1 (with latest Windows Updates): Home Premium, Professional, Enterprise, Ultimate
- 1.8 GHz or faster processor. Dual-core or better recommended
- 2 GB of RAM; 4 GB of RAM recommended (2.5 GB minimum if running on a virtual machine)
- Hard disk space: up to 130 GB of available space, depending on features installed; typical installations require 20-50 GB of free space.
- Hard disk speed: to improve performance, install Windows and Visual Studio on a solid state drive (SSD).
- Video card that supports a minimum display resolution of 720p (1280 by 720); Visual Studio will work best at a resolution of WXGA (1366 by 768) or higher.
Create Source and Destination Connections
Before we can begin to setup the source and destination components, we will need to create two Connection Managers. Since our source and target is a SQL Server database, we will use OLE DB Connection for both Connection Managers.
- Right-Click in the Connection Manager and Select New OLE DB Connection from the pop-up menu option.
- When the Configure OLE DB Connection Manager dialog appears, Click the New button.
- Once the Connection Manager dialog appears, Type or Select the Server Name.
- Double-Click on the OLE DB Source to open the Editor.
- Click the Connection Manager
- From the OLE DB connection manager list, Select the SQLSourceConn that we created earlier.
- Select SQL command for the Data access mode.
Enter the following SQL Statement in the SQL command text box (See Figure-05).
SELECT * FROM SALES.CUSTOMER
Click the OK button.
- In the Reference Column, click the ellipsis button to open the Reference Setup dialog.
- Select SQLSourceConn from the drop-down box for the Connection option.
Set the SQL Query option and type the following query in the text box:
SELECT T1.CustomerID ,DATEDIFF(dd, T1.[OrderDate], T2.MaxOrderDate) NunmberOfDays FROM [Sales].[SalesOrderHeader] T1 ,(SELECT MAX([OrderDate]) MaxOrderDate FROM [Sales].[SalesOrderHeader]) T2 ,(SELECT CustomerID, MAX([SalesOrderID]) MaxOrderID FROM [Sales].[SalesOrderHeader] GROUP BY CustomerID) T3 WHERE T1.CustomerID = T3.CustomerID AND T1.[SalesOrderID] = T3.MaxOrderID
Click the Green Check Box .
- Select CustomerID from the drop-down box for the Key Column option.
- Select NumberOfDays from the drop-down box for the Value Column option (See Figure-08).
- Click the OK button to return.
- Enter the value 91 for the On NULL and No Match property.
- Click the OK button to return to the Data Flow.
Note: The SQL query calculates the number of days since an order was placed. If the input is undefined, it returns the value from the On NULL property and if the input doesn’t produce a match, it returns the value from No Match property (See Figure-09).
- Double-Click on the Derived Column Transformation to open the Editor (See Figure-11).
- Type ActiveFlag for the Derived Column Name.
- Expand the Column folder and drag the CustomerID to the Expression box.
Type the remaining formula in the Expression box:
[NunmberOfDays] >= 90 ? 0 : 1
Click the OK button.
- Double-Click on the OLE DB Destination to open the Editor.
- Select SQLTargetConn from the drop-down box for the OLE DB connection manager option.
- Select Table or view – fast load for the Data access mode.
- Select [dbo].[Src_Customer] from the drop-down box for the Name of the table or the view option (See Figure-13).
In this Quick Start article, I have shown you How-To create an SSIS package that performs a lookup using COZYROC’S Lookup Plus component to return a calculated value from a custom SQL query. Then how to use the returned value in an expression within the Derived Column Transformation, which is a standard component of SSIS, to create and set the value of the new column prior to loading the OLE DB Destination table.
Use the Main dialog parameters below to configure the Lookup Plus component for processing.
Specify the component name.
Specify the component description.
The lookup definitions are set up in the lookup definition grid.
- Use the refresh command to update the lookup definitions with the latest metadata.
Select input column for lookup key.
Set up lookup reference.
Related Topics: Reference Setup
Specify the lookup result output column name.
Specify the output value when the input is undefined. Insert white space to return NULL result. You can reference input column by specifying column name in square brackets like this: [column]
Specify the output value when the input doesn't produce a match. Insert white space to return NULL result. You can reference input column by specifying column name in square brackets like this: [column]
The reference setup dialog is used to create the lookup source. Three source types are available: table or view, SQL query and package variable.
Select the connection type. This parameter has the options listed in the following table.
Value Description OLEDB Use the OLEDB Connection Manager for the Connection option. ADO Use the ADO Connection Manager for the Connection option. ADO.NET Use the ADO.NET Connection Manager for the Connection option. CACHE+ Use the Distributed Cache Connection Manager for the Connection option. 1.5 SR-1
Select an existing connection manager.
Select an existing table or view as the lookup source.
Specify an SQL statement as the lookup source. Use 1.5 SR-1 to create an SSIS expression.
Select an existing package variable as the lookup source. Supported object types are as follows:
Value Description String A variable contains an SQL statement. The first selected column will be used as the key and the second selected column will be used as the value. IDictionary Any object implementing this interface.
Related Topics: Dictionary Destination script, Range Dictionary Destination script.
Recordset ADO Recordset object. The first selected column will be used as the key and the second selected column will be used as the value. DataSet ADO.NET DataSet object. It will use the first table. The first selected column will be used as the key and the second selected column will be used as the value.
Select the lookup source key column.
Select the lookup source value column.
You can match multiple columns using the Lookup Plus component. See the steps below for one possible solution:
- Create a new data flow. This data flow will build a lookup reference dictionary and store it in a package variable.
- Insert a data flow source component.
- Insert the Checksum Transform script after the component from step 2 to create a unique CRC32 value for the match input columns.
- Insert the Dictionary Destination script after the component from step 3. For the key, select the input column containing the CRC32 value. For the value, specify the appropriate reference column. Store the resulting dictionary object in a package variable.
- Create another data flow, which will do the lookup and matching.
- Insert the data flow source with the data that is to be matched.
- Insert the Checksum Transform script after the component from step 6 to create a unique CRC32 value for the required input columns.
- Insert the Lookup Plus component. As the key, specify the input column containing the CRC32 value. In the reference dialog, select the "From Variable" option. Set it to the variable you created in step 4.
Existing packages containing Lookup Plus component and designed with SSIS+ 1.5 or older version are incompatible with SQL 2012. To have successful upgrade follow these steps:
- Install SSIS+ 1.6 or newer on a machine where you have existing packages designed with SQL 2005 or 2008.
- Open the existing package containing Lookup Plus component and re-save.
The saved package will now contain SQL 2012 compatible data and the package is ready for upgrade.
- Fixed: Failed with error "Column data type DT_STR is not supported by the PipelineBuffer class" when trying to process input columns of type DT_STR (Thank you, Megha).
- New: Ability to reference input columns in On NULL and No Match definitions.
- Fixed: Failed to find item if input column and lookup key column types didn't match.
- New: Component now uses multi-threaded lookups loading.
- New: Component now supports SQL statement from variable.
- New: Component now supports binary array as key.
- New: Component now supports SSIS expressions in the query statement.
- New: Introduced component.
Ready to give it a try?
COZYROC SSIS+ Components Suite is free for testing in your development environment.