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.


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


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. 


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.


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.


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


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.


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



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


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

Please, provide your inputs.

Enjoy !!!



Step by Step SSIS Multicast Transformation TIP #121

Dear Friends,

This is another post in the series of step by step SSIS ,Zero to hero series. In this post we will discuss another transformation control which is Multicast transformation.

Multicast is a way which help us to provide multiple copy of same source data for different transformation. Or in other words we can say multicast transformation create multiple pipelines for the same data.

Lets understand this by an example. Suppose, we are product developer and selling product in different state in India. Now ,the problem is same product may have different selling price due to different tax system. So, we have to use same data of product but there will be different discount according to sate tax.

Now, here we go step by step. for your information in this example we are going to use similar example which we did in last post of Derived column Transformation tip #120

Step 1:- Add a package and drag drop data flow task and configure source database. I am taking Adventurework2012 database and using product &  productCategory tables for source data query below is simple snap of data


Step 2:- Once the data is configured. Drag drop Multicast control  as shown in below figure and give output of source object to Multicast and try to configure it.


Step 3:- Now, when we have configured Multicast we can get multiple output from Multicast control. Suppose , One state is taking 10% WAT tax, another state taking 20% WAT tax and last one is taking no tax (wow that’s great). So we can drag drop derived column and configure then according to 10% ,20% WAT tax for reference you can take a look of tip #120 

I configured the the 3 different output in same way and adding the respective flat files. As shown in below figure you will see we added 3 files

1) with 10% ,with 20 % and without and tax


After configuration you will get structure something like as shown in below image. As a result we will get 3 flat files with 10% ,20% and without any change.


Step 4:- If you face any difficulty in Derived column configuration and moving the data to file please follow earlier post. Now once everything is configured run the package by pressing F5 or hitting run button you will get following screen. So , if you see Multicast providing similar row count to different output which is 295 rows.


We , can use this Multicast where we need multiple copy of same source data.

I hope this article might help you somewhere.

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.


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


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.



Step 4:-

Now, drag drop flat file destination and configure it.


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


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


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

Enjoy !!


How to deploy SSIS Package ? Step by Step SSIS TIP #114

Dear Friends,
In last post we have successfully created our fist basic package. So, Now the next thing is how to deploy this package.

We have created this package for someone else or our client so we need to run this package on his /her machine.

For this we need to know how to deploy. Lets start this step by step 

Step 1:- Open the existing solution and right click on the solution you will get following options as shown in figure. You need to click on “Convert to Package Deployment Model”   as highlighted below


Step 2:- Once you click it you might get popup for confirmation just click OK.


Step 3: Now click on Properties you will get following screen in which you need to select the deployment option. And need to select true value for CreateDeploymentUtility as highlighted below in figure. Once You make this option true  press OK or Apply Button.


Step 4:- Once you done with this again right click on the solution and again click on Covert to Package deployment model.  Now this time you will get Wizard screen as shown below. Click on Next button


Step 5:- You will get below screen which is package selection screen. (Suppose you have multiple packages in a solution so for which package you want to create deployment you need to check / Uncheck according to your need.) Once you selected the Package click on Next button.


Step 6:- When you click next button you will get next option which is specific project properties as shown below in screen. In this we have an important option which is Protection level. It is use to set the level of showing sensitive data in package. for example a package may contain sql server connection string in which we have username and password and username and password are very important and sensitive information. Here ,with protection level we have option to encrypt this sensitive data or make package password protected. This will be an interesting topic which we will discuss separately in near future in separate post. for current time being just leave the default option as is and click on next button.


Step 7:- When you click on next button you will get following screen. This is the screen where you can call another package which this deployment. We will discuss it later for current deployment process we don’t want to call any other package.  Just click on next button.


Step 8:- When you click on next button you will get following screen which says about configurations. We can create a configuration file which is just a simple XML file. If you are a .NET developer just assume it web.config file where we can keep connection string or other configurable item.


Step 9:- We can create parameters which can be use to pass the values in packages. In current solution we have not configured even parameters so don’t think to much about this currently. We will discuss it separately in coming post.


Step 10:- Now directly jump to Perform Conversion and skip review step. and click next you will get following screen of popup. Press OK and then try to rebuild the solution.


Step 11:- We did  a build/rebuild to cross check package is correct. Now open the deployment folder in bin folder (You can find the address in step 3’s screen) . When you open the folder you will get two files one is Your package file with DTSX extension and another one which is called manifest file as shown in the figure below. Below ExportSQLToFlatFile is our manifest file  and Package is our actual DTSX package file. (if we have config file then you will find config file as well)


Step 12:- Now, we successfully completed our first part of deployment which is basically creating a package deployment file which is a manifest file. We can copy these files on any server where we need to deploy the package. When you click on Manifest file which is ExportSQLToFlatFile you will get following screen


Step 13: Click on Next button when you get above screen you will get following screen which shows 2 options of package deployment

1) File System deployment 2)  SQL Server deployment

Now for current demo we are using file system deployment and clicking next button.


Step 14:- When we click next button we get the screen which ask for folder where package will be deploy by default it is SQL SERVER’s DTS package folder so let it be currently and click Next button


Step 15:- After clicking next button , we will get following screen. Which is a confirmation screen for deployment.


Step 16:- Now the last step click the finish button and we are good to go.


So all the above steps are for creating a deployment for package and then deploy in SQL SERVER. Now , I am sure your next question will be what now ?

So, now we call this Package in SQL SERVER Job and configure job which execute this package time to time.

We, will discuss it more in detail coming post.

I hope you will like this post. please do post your inputs.



WWH in SSIS ? Zero to Hero in SSIS series TIP#111

Dear Friends,

As shared in last post Post #110, We are starting SSIS tutorial series ( A Step by Step  SSIS learning tutorial) from today.

Before starting anything we might have some questions like What is SSIS ? Why SSIS ? & How to use SSIS ?

In general term I called this “WWH” What, why  How ?

So, the straight forward answer is SSIS is SQL SERVER Integration Service (a Microsoft tool). It generally comes with SQL Server license. Although It is not always mandatory to have this SSIS tool with SQL Server license, You can install it standalone as well. SSIS is a ETL (Extract Transform loading) tool. We can can use SSIS with IDE which provided by Microsoft.

Now, I understand this is too straight forward and we are not able to digest easily. Let me explain it with an example.

I am leaving in Indore a city reside in Madhaya pradesh a state in the heart  of India. Suppose the Chief Minister of MP allocated some funds to Municipal corporation of  each cities like Indore, Ujjain, Bhopal etc. to do whatever best they can do with money and make their city a better place.

After few years The Chief Minister realized and thought let’s ask each cities how they utilized the money. What are the different areas on which they have work with the provided money.

The chief minister wants consolidate reports (which we generally called MIS reports) as well as some detailed reports.

Now, suppose different municipal corporations using different ways to restore data like Indore municipal corporation using Microsoft SQL SERVER, Bhopal municipal  corporation using ORACLE, Ujjain’s municipal corporation using  MYSQL, Devas municipal corporation using flat file system.

So there might be different heterogeneous systems used to maintain the data and data would be on different verticals like Transportation (Road & Bridges), Water harvesting (River  & tanks) ,Agriculture , lighting etc.

To grab this entire data & consolidate them in one single database and providing the reports to Chief minister so that he can analyze which city doing progress , which city consuming money , how much work done in different vertical so that , he can take further decisions.

I hope you understand  a big problem here which is gathering data from different heterogonous resource & compile them and provide a unique data base on which reports will create is a big task which can be easily done by an ETL (Extract Transform Loading) tool which is SQL SERVER Integration Service Tool.

Here Extract means extract or fetch data from different resource or data source whether it is SQL SERVER, ORACLE, MYSQL , Flat file or any other source

Transform means converting the data in to required single format

Load means prepare the data and provide final output.

Below is the image which illustrate same thing which we discussed earlier in this post.


So , I hope you understand the SSIS (ETL) role here.

Remember here SSIS use here to provide the data only rest work will be done by SSAS (SQL Server Analysis Services) and SSRS (SQL SERVER Reporting Services)

In this post we got 2 answers 1) What is SSIS ?  2) Why is SSIS? we will see How to use SSIS in next post.

Till then

Enjoy !!!

RJ !!!