The Unconventional Guide to How to Implement Lookup Transformations TIP #124

This is one the articles in the series of step by step SSIS tutorial. In this post, we are going to understand Lookup transformation and also see an example to implement Lookup transformation in 8 easy steps.

Now, Lookup transformation is one of the interesting transformation which is used to implement equijoin between data sources.
We are discussing the same example which we discuss in the earlier post which is Person & PersonPone tables of AdventureWorks.

Suppose, we want Person details and the phone number of person as a result then we can apply Lookup transformation.

When, we applied the Lookup transformation between Person & Person Phone table then we will get two result set.

One of the result sets is the Matched result set in which we will get the Person data and the First Matched Phone number while the PersonPhone table might have multiple phones for a person but we will get only one phone number record because Lookup transformation uses EquiJoin.

In the second result set, we will get unmatched Person records which mean the Person who doesn’t has a Phone number in PersonPhone table.

I think, we have a basic understanding of what is Lookup Transformation. Now, we are going to implement Lookup Transformation step by step.

Step 1:-
So, the first basic step is to add a package and drag-drop data flow task as shown in below figure. You can rename the  data flow task  as I did in below figure.

1_DataSource

Step 2: – In this step drag drop a data source and configure it for Person table as shown in below figure. If you face any difficulties in this then you can review previous articles of this series

2_SqlSource

Step 3:- Once the Person table is configured just drag drop Lookup Transformation control and provide the input of Person table Data source to Lookup table. When you try to configure the Lookup you will get the following screen. We will discuss Cache mode separately in detail. In this example, we are using Full Cache and connection type to OLEDB connection Manager.

Now, Click on Connection option on the right and configure the PersonPhone table. 
4_Lookup

5_LookupTransformationColumn

Step 4:- Now, configure the Columns relationship between Person & PersonPhone table.To achieve this just drag-drop BusinessEntityId of available input columns of Person table to available Lookup columns which are in PersonPhone table and matched with BusinessEntityId which is common between Person & PersonPhone table. Here we have to select PhoneNumber column which we have to include in the result set.

4_Lookup_111

Step 5:- Now, Drag drop two excel destination source and configure it. As, I did in below figures. Here one Excel is with name LookupOutput.xls which contain matched records.

7_ExcelOutput

Below, is another Excel which contains unmatched records. Here UnMatched means the Person records who don’t have phone numbers.

8_Lookupoutputnotmatched

Step 6:- Now, If you see there are 3 output lines coming from Lookup transformation.So, First blue output gives it to Lookupoutput excel which will have all the matched records of person & Person Phone number. You can configure inputs as shown below.

9_Matched_Excel

Step 7:- In a similar way, we have to configure unmatched record output. Once, we done with configuration of both Matched & unmatched output, we will get following structure as shown in below screen

10_NotMatched_Excel

10_Final

Step 8:-  Once, everything is configured you can run the package and if everything is perfect then you will get the following screen

finalRun

I hope above steps are easy and you can implement Lookup Transformation at your end too.

Please, provide your inputs.

Enjoy !!!

RJ!!!

Advertisements

How to do Step by Step Merge Join Transformations – #123

 

In last post tip #122, We discussed Merge transformation. Now in this tip, we are going to discuss Merge Join. Although, it might be confusing sometimes Merge & Merge Join. So, let me explain it here.

As explain earlier merge is like a union operation in which it just collect all the records from provided sources without any condition. suppose if we have Person & PersonPhones two sources as shown below

Below is Person table with two columns personId & Name

PersonId Name
1 Ram
2 Shyam
3 Ghanshyam

Below is PersonPhones table with two columns PersonId & Phone

PersonId Phone
1 9999
2 3122
3 3422

Now if we talk about merge transformation then the result would be as shown below

Merge Transformation output

PersonId Name Phone
1 Ram  
2 Shyam  
3 Ghanshyam  
1   9999
2   3122
3   3422

Now, If we talk about Merge Join transformation for above same inputs (Person, PersonPhones) then we will get following output

PersonId Name Phone
1 Ram 9999
2 Shyam 3122
3 GhanShyam 3422

So, Merge Join is basically a join like Inner join, Left outer join, Full outer join etc.

I hope you got the context. Here we go step by step to implement the Merge join transformation

Step 1:-  Add the different sources which you want. For this example I am taking two sources which have AdventureWorks database and one source have Person table & another one have PersonPhone as shown above in the example.

We added data flow task as shown in below image and renamed it to Merge join data flow task.

DataflowTaskStep1

Now, in this data flow task, we will add data sources, in the first source we use person table and in the second source we use PersonPhone.

from person table, we are choosing BusinessEntityId, FirstName,MiddleName, LastName,suffix as shown in below figure

MergeSource1Step2 

In the other source, we are using BusinessEntityId & Phone number columns.

MergeSource2Step3

If you find any issue in above steps then please, follow previous articles of step by step SSIS on Indiandotnet.

Step 2:- Now once the source is configured, we are going to drag drop merge control as shown in below figure.

DragDropMergejoinStep4

Step 3:- Now to configure Merge join, we are going to drag drop inputs from the sources. When you try this at your end then you will get the following screen. Here you have to set left input and right input for the merge join.

ConfigureMergeJoin4

Now, When you tried it you might found following warning. Which means the provided inputs of datasource1 & datasource2 are not sorted.

WarningforSortStep5

Step 4:- Here you have the option to add a Sort transformation and then provide input to merge join transformation control which is fairly simple and we have discussed in previous tips. Here, I am sharing one more option.

In this, you have to right click on DataSource and choose option Advance Editor by which you will get the following screen. You have to select “OLEDB Source output” of “Input and output Properties” tab.

Here, we need to make the IsSorted property to True as highlighted in below snap.

IsSortedProblem6

Step 5:- Once the above Step is done next step is click on output column’s BusinessEntityId value. Here you have to set the SortKeyPosition to 1.

As highlighted in below screen.

BusinessEntitySortKEy

Step 6:- Once you configured sorted input sources then next step is to configure the merge join. So when you click on Edit option of merge join. You will get the following screen.

Here if you see, we can configure join type like Inner join, Left outer join, Full outer join.

For current example, we are choosing the option “Inner join”  and joining key is BusinessEntityId which is primary key in Person table and foreign key in PersonPhone table.

ConfigureMergejoinTypesStep6

Once the configuration is done. We can select what are the columns which we need as an output.

Step 7:- Now, Next step is to configure the output in excel. for this, we drag & drop a destination source. In the current example, we are using Excel as a destination. We are mapping the columns of output to excel as shown in below figures.

ConfigureExcelDestination

MappingInexecel

Step 8:- Once, everything is configured. We run the package and if everything is working fine then we will get the following screen.

MergeJoinRunResult

To cross check the result we open the excel and we get the output which we expected as shown in below figure.

ResultOutputinExcel

I hope this article might help you to understand the Merge join.

Enjoy !!!

RJ!!

Step by Step SSIS–Aggregate Transformation TIP #118

 

Dear Friends,

In the series of step by step SSIS tutorial this is another post. In this post we will see Aggregate Transformation. I am pretty much sure you are aware of aggregation. Although , Just wanted to share that aggregation operation in generally memory expensive operation.

So, whenever you want aggregation & want to use group by function then in such situation you can use Aggregation transformation.

There are different group by option available like MIN, MAX , COUNT, SUM, AVERAGE etc.

Let’s understand how to use Aggregate transformation step by step.

For current example we are using Adventureworks database and we are using below query. Here we are fetching the product data with line total,unit price  & other details.

Data

if you see the records in the table you will find that there are multiple records for same product with different line total.

Our objective is to aggregate or do sum of Line total according to Line Number and export the result in a csv.

Step 1:- So , Now start with package creation add a new package in solution and drag drop source Assistant and configure the database connection as we did earlier in the tutorials

Step 1.1 – Drag drop  Data flow task

Step 1

Step 1.2 – double click data flow task  and drag drop source assistance control

Step 1.1

Step 1.3 – Configure source assistance

Step 1.2

Step 2: Once the source assistance is configured with SQL SERVER connection string and specific query we will drag drop Aggregate Transformation control as shown in below figure

Step2

Step 3:- Now configure this aggregate control. So Just right click aggregate control and select Edit option. Now as we require Sum of LineTotal so we have selected SUM in operation column’s drop down and rest other has drop down option group by.

 

Step3

Step 4:-

Now, drag drop flat file destination and configure it.

Step4

Now configure flat file  with mapping as shown in below figures

Step 4.1

Step 4.2

Step 5: Now run it you will get desire result as shown in below figure

Result

If you see in the result we got 757 rows after processing 121,317

Now see the result in actual as shown in below file

File

I hope this example might help you to understand Aggregate transformation. Please provide your inputs.

Enjoy !!

RJ!!!

Zero to hero in SSIS (SQL SERVER Integration Services) TIP #110

Recently, Many friends of mine are interested in learning SSIS so , I thought to write some blogs which might help them.

I selected following area on which I will write blog on SSIS. It would be great if you also share your inputs.

Step 1: WWH of SSIS & Architecture of SSIS ? (What , Why, How ) 

Once you understand What, Why, How you are curious to know how to create a basic program /package in SSIS. I am sure you might have used this basic SSIS package but you might not aware (Import/Export) in SQL Sever.

Step 2:- How to create my first basic package using SSIS ?

With step 2 you will be more comfortable and confident that you can create package. Once your first package is created the next step which I think  is deployment. How to deploy in real world and use it.

Step 3:- How to deploy SSIS package and different way of deploying ?

Step 4:-  How to create basic data flow task package ?

              With data flow task we will try to understand below common controls as well

                 4.a – Data Conversion

                 4.b -  Conditional Split

                 4.c – Derived column

                 4.d – Lookup

                 4.e – Merge

                 4.f – Merge Join

                 4.g- Multi cast

                 4.h – Row Count

                 4.I – Sort

                 4.j – Union all

                 4.K – Others (remaining)

 

Step 5:-  Understand For Loop Container

Step 6: Understand for each Loop container

Step 7: Sequence  container

Step 8:-  Bulk Insert Task

Step 9:- Script Task

Step 10:- Web service Task

Step 11: XML  Task

Step 12:- File System Task

Step 13:- Execute Process task

Step 14:- WWH variables? How to define application variable ?

Step 15:-  Performance improvement of SSIS Package

Step 16:- Interview questions related to SSIS

I hope with above blog steps those my friends will be benefited.

 I appreciate your inputs as well what else we can include in this series .

Enjoy !!!

RJ !!!