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 than

Enjoy !!!

RJ !!!