Introduction to ssis

SSIS is a tool used for ETL. ETL stands for Extract Transform and Load. These are simple day-to-day words we use in our daily lives. The figure below depicts ETL in real world scenario.

E-Extract Data:

Extract Data from various homogeneous or non-homogeneous source systems. Data could be stored in any of the following forms though not limited to them. Flat file, Database, XML, Web queries etc.

T-Transform Data:

The data are coming from various sources. We cannot assume that the data is the structure in the same way across all the sources. Thus, we need to transform the data to a common format so that the other transformations are done on them. Once we have the data we need to perform various activities like:

Data cleansing
Mandatory check
Data type
Foreign key constraints
Business rules and apply business rules
Creation of surrogate keys
Sorting the data
Aggregating the data
Transposing the data
Trim the data to remove blanks.

The list can go on as the business requirements get complex day by day. Hence the transformations get complex. While transformations are we need to log the anomalies in data for reporting

L Load Data:

Once the transformations were done and data takes the form as per the rule. We have to load the data to the destination systems. The destinations can also be as varied as the sources. Once the data reaches the destination, it is consumed by other systems.

SSIS stands for SQL Server Integration Services. Microsoft introduced Business Intelligence Suite, which includes SSIS, SSAS, and SSRS.
Now, what’s this Business Intelligence (BI)? Let me take some time to explain that. As the name suggests, it helps Business run across the globe. It provides the business with data and ways to look into the data and make business decisions to improve
So, how do the 3 products work in the BI world or how are they organized? To start any business analysis we need data. ETL would be used here to get the data from varied sources and put the data to tables or create Cubes for the data warehouse. At one point we have the data with us, SSAS comes into the picture to arrange the data and stores them to cubes. Next, we need to report the data so that it makes sense to the end user. This is where SSRS comes into picture for announcing generation.