What is SSIS?
SQL Server Integration Services(SSIS) is a platform that performs wide range of data integration and data transformation tasks.
SSIS is a component of Microsoft SQL Server and is the evolution of a SQL Server module previously called Data Transformation Services (DTS). SSIS features a data warehousing tool that allows to develop data integration and workflow solutions. It is a flexible tool use to perform ETL operations i.e. data extraction, data transformation and loading the data. The tool can also be used to automate maintenance of SQL Server databases, update multidimensional cube (analytical) data, and perform other functions.
Uses of SSIS
- Data can be stored in many different forms and it can be in structured, semi-structured or an unstructured form. Integration Services can connect and extract these data from all sources, transformed to a compatible format and merged it into a single dataset or package.
- Population and maintenance of data warehouses and data marts. Due to exceptionally large volume of data and the window of time to perform ETL tends to be short, SSIS can bulk load data directly from flat files in SQL server and has a destination component that can perform a bulk load in SQL server.
- SSIS can rerun the package from a task or container within a control flow that can handle various errors due to complex data loading.
- Also, SSIS packages can merge data into a single dataset and load dimension table in a single process.
- SSIS handles the complex updating scenarios with the help of Slow Changing Dimension Wizard that dynamically creates data transformation tasks and manage, update and insert records, update related records and add new columns to tables.
- If the data warehouses and data marts include aggregated information, SSIS includes data transformations that can apply different calculations or aggregations depending on the different areas of dataset.
- Occasionally data formats may differ, SSIS includes data formation tasks to performs wide range of data-cleaning, converting and improve functions. SSIS can also identify records that may be duplicates by using SSIS grouping transformation.
- Operational Administrative functions that needs automation. SSIS includes entire set of tasks specifically designed to copy SQL server objects, bulk loading of data, restoring databases and backing up. Users can also have access in SSIS to a SQL Management Objects(SMO) enumerator to perform administrative operations on each server.