Lookup Transformation in SSIS

Now-a-days, organizations hold large volumes of data. For handling the data and its transformations, organizations are looking for the platforms that help in performing data transformations leading to their business growth and success. With utmost concentration on the enterprise based platforms, the businesses have moved their focus on to the SQL server integration services, shortly called as SSIS. SSIS is an enriched platform that is helping in performing data transformations and integrations to resolve the complex business issues by cleansing, loading warehouses, downloading or copying the files, etc. In this article, we will discuss Lookup transformation in SSIS and the steps used to create it with multiple conditions. Let’s get started!

What is Lookup?

Lookup is considered as one of the powerful components in SSIS which helps in performing the lookup operation by establishing a connectivity between the input value either with the table dataset columns or the data-table.  Lookup will perform the comparison between the table set that is existing and the source data, responsible for sorting and filtering the matching ones.

What is Lookup Transformation?

A lookup transformation is responsible for performing the lookups by collaborating data that is present in the input columns with the data present in the columns of a reference data set. In order to access the additional information that is present in the referred table or related table, a lookup is used based on the values that are present in the common columns.

A reference data set can be either a cache file or an existing view or table, or a new table, etc. The configuration of the lookup transformation can be done in the following ways:

  1. By specifying the view or table that includes the reference data set.
  2. By specifying the joints that exist between the reference data set and the input.
  3. By adding the columns to the lookup transformation output from the reference data set.
  4. Cache options configuration.

Become a SSIS Certified professional by learning this HKR SSIS Training !

SSIS Training

  • Master Your Craft
  • Lifetime LMS & Faculty Access
  • 24/7 online expert support
  • Real-world & Project Based Learning

Real-time scenario:

Let us consider a real time for a better understanding about SSIS lookup transformation.

Let us take a case scenario in which you design a table called dbo.books which includes the below entries.

SSIS lookup transformation
In the above represented table, the book ID column is considered as the identity column along with a primary key. You need to copy all the content that is present in the above table into another table called the destination table which can be named as dbo.bookhistory. Below is a list of the conditions:

Condition - 1: Insertion of all the records if the record is not available in destination

The first condition states that the SSIS package should copy the records or contents when the destination table doesnot include any information or records which likewise the source table.

The destination table will include the below set of contents, if the first condition is executed as expected in the SSIS package, 

first condition

Condition 2: Insertion of new records into destination table

The SSIS package should include or insert a new record into the destination table only when there is a book entry available  that is present in the destination table entry that is done in the source table. All the duplicate records need not be inserted in the destination table.

In the below example, we see an additional entry that is inserted into the source table.

destination table

Previously, the source table included only 6 rows, and after the SSIS package execution took place, a new entry was added.

Condition 3: Only the relevant records are updated in destination table

The destination table will be updated if a change is present in the content or the records of the source table.

Let's take an example of the book price changing. We need to update the price of the book, hence a specific row needs to be updated in the destination table as well. The SSIS packet should not update any other rows in the destination table except the book price row.

The below image represents the book prize lowered to 510.

book prize lowered

To specify certain conditions, you can make use of T-SQL code which is a complex thing.

Creation of SSIS package:

To gain an understanding about the creation of the SSIS package for lookup transformation, let us create a source table along with some data entered into it by using the following  set of queries.

lookup transformation

Below is the code that is used for creating the destination table but to not include any sort of data into it.

creating

Want to know more about SSIS, visit here SSIS Tutorial !

Subscribe to our youtube channel to get new updates..!

Creation of SSIS package based on conditions:

Condition- 1: 

Below are the steps that are used for creating an SSIS package

a. Navigate to the SQL server data tools and click on create for a creation of a new integration project.

b. You need to drag the data flow task from the toolbox of SSIS to the control flow

SQL server data tools

c. You need to right click upon the data flow and have it renamed as SSIS lookup transformation

data flow

d. Once you Double click on the task,you will be navigated to data flow tab available.

data flow tab

e. You need to drag the database source and configure it.

database source

f. Once the configuration is completed, the source can be renamed.

source can be renamed

g. A lookup task needs to be added  and then join the source data task  by using a green arrow.

lookup task

 h. Double click on lookup task for configuring it which will open up the lookup transformation editor.

i. You need to specify the cache type and the connection manager.

connection manager

j. You can specify the way to handle the rows without any matching entries in the dropdown.

matching entries

In the first condition, we have discussed whether rows from the source to the destination table will be inserted only if the destination table does not hold any records. The redirect rows to no match output will be helping in comparing both of the tables, identify the differences between the tables and insert the rows to desired destination table.

desired destination table

Navigate to the connections tab and provide destination table using drop down.

Navigate to the connections tab

You can click on columns available which shows the destination table on the source table. Our intention is to compare both the bookid columns that are  present in both the tables. You can just track the book_ID column that is present in the source table to the book ID column present in the destination table. It is connected with an arrow  represented below.

book ID column

You need to click on Ok with which the icon representing the red cross will disappear.

icon representing

You need to add the database destination and then join the lookup task with the destination, which will open up the input output selection.

You need to select the values for output as lookup no match output and for input as the destination input.

input output selection

Click on Ok and complete the configuration of the destination table.

The arrow that exist between the destination and lookup will show that looking no match output

match output

For package execution, click on start. You can see that the 6 rows of data are transferred from the source table to the destination table because the destination table does not include any sort of information before the package gets executed.

package execution

Top 50 frequently asked SSIS Interview Questions !

SSIS Training

Weekday / Weekend Batches

SSIS package execution for condition-2:

To execute the package for the condition-2, you need to add a new entry of record in the source table. Let us say that the bookID 7 is the new record that has been added into source table.

Here destination table does not include the new record that is present in the source table as represented below.

source table

On the package execution, you can review the following:

  1. Source Table includes only 7 rows
  2. The lookup transformation will check for the record that are available in the source and destination table, and will only include the new record added in the destination table

lookup

SSIS package creation for condition 3:

Let us consider that we have updated a certain value or the record in the source table and would like to update in the destination table as well.

The primary step is to execute the code for updating the value of the record like price.

After the price is updated, the next step is to drag the command task and also join the lookup.

primary step

You need to rename the command task as updated records

updated records

Select the connection in the connection manager that is available in the dropdown.

connection in the connection manager

Make sure to add the command in the component properties option.

The question mark is represented for parameters

component properties

You will have column mappings in the next available tabs. The columns need to be mapped as per Stored procedures.

column mappings

Click on Ok, you will be able to see SSIS package.

SSIS package

Once you Execute the SSIS package, updates will take place and 7 records will appear in the definition table.

7 records

In the below table, you can see that all the records or values in the source table are now available in the destination table.

source table are now available

Conclusion:

In this article, we have gone through lookup, lookup transformations, step by step to process to create the SSIS package and execute them based on the different conditions. I hope this information is helpful to you.

Related blog: 

MSBI Incremental load in SSIS

SSIS Sample Resume

Find our upcoming SSIS Training Online Classes

  • Batch starts on 2nd Oct 2023, Weekday batch

  • Batch starts on 6th Oct 2023, Fast Track batch

  • Batch starts on 10th Oct 2023, Weekday batch

Global Promotional Image
 

Categories

Request for more information

Gayathri
Gayathri
Research Analyst
As a senior Technical Content Writer for HKR Trainings, Gayathri has a good comprehension of the present technical innovations, which incorporates perspectives like Business Intelligence and Analytics. She conveys advanced technical ideas precisely and vividly, as conceivable to the target group, guaranteeing that the content is available to clients. She writes qualitative content in the field of Data Warehousing & ETL, Big Data Analytics, and ERP Tools. Connect me on LinkedIn.

In SSIS, Lookup transformation is beneficial and allows you to perform lookups connecting the data within the input columns with the reference dataset columns. It helps compare the source and destination data.

The following benefits you will get by using Lookup transformation within SSIS.:-

You can use it to retrieve additional information within a linked table based on the common column values. Also, it supports various database providers such as SQL Server, DB2, and Oracle for OLE DB connection manager.

The following steps will involve using the Lookup Transformation within SSIS.

You must select the relevant connection manager you wish to use in this transformation. You can choose the OLE DB connection manager if you wish to connect with a database. Otherwise,

you can select a “Cache connection manager” if you wish to associate with a cache file.

State the relevant table that includes the reference (cited) dataset.

Then produce the cited (ref) dataset by stating an SQL (Structured Query Language) statement.

Mention joins that link the input with the relevant reference dataset.

Then you need to add some columns to the Lookup transformation product from the cited (ref) dataset.

Later design the options of caching.