|
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!
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.
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:
Become a SSIS Certified professional by learning this HKR SSIS Training !
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.
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:
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,
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.
Previously, the source table included only 6 rows, and after the SSIS package execution took place, a new entry was added.
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.
To specify certain conditions, you can make use of T-SQL code which is a complex thing.
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.
Below is the code that is used for creating the destination table but to not include any sort of data into it.
Want to know more about SSIS, visit here SSIS Tutorial !
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
c. You need to right click upon the data flow and have it renamed as SSIS lookup transformation
d. Once you Double click on the task,you will be navigated to data flow tab available.
e. You need to drag the database source and configure it.
f. Once the configuration is completed, the source can be renamed.
g. A lookup task needs to be added and then join the source data task by using a green arrow.
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.
j. You can specify the way to handle the rows without any matching entries in the dropdown.
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.
Navigate to the connections tab and provide destination table using drop down.
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.
You need to click on Ok with which the icon representing the red cross will disappear.
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.
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
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.
Top 50 frequently asked SSIS Interview Questions !
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.
On the package execution, you can review the following:
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.
You need to rename the command task as updated records
Select the connection in the connection manager that is available in the dropdown.
Make sure to add the command in the component properties option.
The question mark is represented for parameters
You will have column mappings in the next available tabs. The columns need to be mapped as per Stored procedures.
Click on Ok, you will be able to see SSIS package.
Once you Execute the SSIS package, updates will take place and 7 records will appear in the definition table.
In the below table, you can see that all the records or values in the source table are now available in the destination table.
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:
Batch starts on 24th Mar 2023, Fast Track batch
Batch starts on 28th Mar 2023, Weekday batch
Batch starts on 1st Apr 2023, Weekend batch
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.
©2023 HKR Trainings. All rights Reserved.