Lookup

Requirements

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:

Operating Systems

• 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

Hardware

• 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.

1. Right-Click in the Connection Manager and Select New OLE DB Connection from the pop-up menu option.
2. When the Configure OLE DB Connection Manager dialog appears, Click the New button.
3. Once the Connection Manager dialog appears, Type or Select the Server Name.

4. Then, Select the Database Name and Test Connection.
5. Click the Ok button to create the Source Connection.
6. Repeat steps 2 through 5 for the Destination Connection.
7. After the Source and Destination Connections have been created, rename them to SQLSourceConn and SQLTargetConn.

Setup Data Flow – OLE DB Source Component

1. Drag and drop the Data Flow Task Plus from the SSIS Toolbox to the design surface (See Figure-03).

2. Click the Data Flow tab.
3. Add the OLE DB Source component from the SSIS Toolbox, by dragging the component to the design surface (See Figure-04).

4. Double-Click on the OLE DB Source to open the Editor.
5. Click the Connection Manager
6. From the OLE DB connection manager list, Select the SQLSourceConn that we created earlier.
7. Select SQL command for the Data access mode.

8. Enter the following SQL Statement in the SQL command text box (See Figure-05).

   SELECT * FROM SALES.CUSTOMER
   

9. Click the OK button.

Setup Data Flow – Lookup Plus Component

1. Add the Lookup Plus Component from the SSIS Toolbox, by dragging the component to the design surface.
2. Drag the connector arrow from OLE DB Source to the Lookup Plus Transformation (See Figure-06).

3. Double-Click on the Lookup Plus to open the Editor.
4. Select CustomerID from the drop-down box for the Input Column option (See Figure-07).

5. In the Reference Column, click the ellipsis button to open the Reference Setup dialog.
6. Select SQLSourceConn from the drop-down box for the Connection option.

7. 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
   

8. Click the Green Check Box .

9. Select CustomerID from the drop-down box for the Key Column option.
10. Select NumberOfDays from the drop-down box for the Value Column option (See Figure-08).
11. Click the OK button to return.

12. Enter the value 91 for the On NULL and No Match property.
13. 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).

Setup Data Flow – Derived Column Component

1. Add the Derived Column Component from the SSIS Toolbox, by dragging the component to the design surface.
2. Drag the connector arrow from Lookup Plus to the Derived Column Transformation (See Figure-10).

3. Double-Click on the Derived Column Transformation to open the Editor (See Figure-11).
4. Type ActiveFlag for the Derived Column Name.
5. Expand the Column folder and drag the CustomerID to the Expression box.

6. Type the remaining formula in the Expression box:

    [NunmberOfDays] >= 90 ? 0 : 1 

7. Click the OK button.

Setup Data Flow – OLE DB Destination Component

1. Add the OLE DB Destination Component from the SSIS Toolbox, by dragging the component to the design surface.
2. Drag the connector arrow from Derived Column to the OLE DB Destination Component (See Figure-12).

3. Double-Click on the OLE DB Destination to open the Editor.
4. Select SQLTargetConn from the drop-down box for the OLE DB connection manager option.
5. Select Table or view – fast load for the Data access mode.
6. Select [dbo].[Src_Customer] from the drop-down box for the Name of the table or the view option (See Figure-13).

7. Click on the Mappings page to map input columns to the destination columns.

Note: The Input Columns and Destination Column should automatically be mapped (See Figure-14).

8. Click the OK button to return to the Data Flow.

9. Click File from Menu and Save All.
10. To run the package, Click Start Debugging on the menu bar, or press F5 key (See Figure-15).

Note: After the package finishes running, press Shift+F5 to return to design mode.

Summary

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.