What is SSIS (SQL Server Integration services)

• It's ETL tool (Extract, Transform, Load)
• Used mostly in Data warehousing projects
• Used for Data Migration projects

List of ETL Tools

• Informatica PowerCenter
• Microsoft SQL Server Integration Services (SSIS)
• Oracle Data Integrator (ODI)
• AWS Glue

AT which phase of datawarehouse ssis is used

• Data Preparation layer is our DataWareHousing layer

• in data Preparation layer there is two phases
  • Stagging
  • Data Warehouse
• SSIS is used within the data preparation layer to move and transform data between different stages of the data pipeline.

In the Data Preparation stage:

• SSIS moves data from various data sources such as databases, files, cloud apps, and Excel files into a staging area within SQL Server.
• After transforming the data in the staging area, SSIS transfers the processed data to the data warehouse (DW).

Following the Data Preparation stage:

• The data flows into the semantic layer, where SQL Server Analysis Services (SSAS) is used to build either tabular or multidimensional models.
• This model is then available for use in Power BI Desktop for creating reports, which are finally hosted on the Power BI Report Server for presentation.

DQS and MDS are both Microsoft SQL Server services used for data quality and data management within an organization's data infrastructure, especially in the data preparation layer before the data is loaded into a data warehouse (DW).

DQS (Data Quality Services (DQS)

• DQS is a knowledge-driven data quality solution in SQL Server that allows organizations to manage the quality of their data through various cleansing, matching, and profiling techniques. It helps ensure that the data being processed and loaded into the system is accurate, consistent, and reliable.

Master Data Services (MDS):

• MDS is a master data management (MDM) solution in SQL Server that allows organizations to manage their critical business data, often referred to as "master data," in a centralized manner. It provides a framework to create, manage, and maintain a single, accurate version of key business entities, such as customers, products, and suppliers.