SSIS is a platform for data integration and workflow applications. It features a fast and flexible data warehousing tool used for data extraction, transformation, and loading (ETL). The tool may also be used to automate maintenance of SQL Server databases and updates to multidimensional cube data. (ref:wikipedia)
While ETL processing is common in data warehousing (DW) applications, SSIS is by no means limited to just DW. At a high level, SSIS provides the ability to:
- Retrieve data from heterogeneous data sources (e.g. SQL Server, Flat Files, Excel sheets, CSV files, Raw files, XML files, SharePoint Lists etc.)
- Perform various transformations on the data; e.g. Aggregation, Copy/Map, Data Conversion, Derived Column, Union, Merge, Join, Multicast, Sort, Pivot & UnPivot, or any other calculations using .net scripting languages (C# or VB.NET).
- Load data into any source (as mentioned above).
- Define a workflow
History: The first version of SSIS was released with SQL Server 2005. SSIS is a replacement for Data Transformation Services (DTS) which was available with SQL Server 7.0 and SQL Server 2000. SSIS builds on the capabilities introduced with DTS.
In the following parts of this thread, I will be covering a number of topics that you need to understand in order to successfully build an SSIS package. High level outlines are undermentioned:
- Creating SSIS packages with SQL Server Management Studio (SSMS)
- Creating a simple SSIS package in BIDS
- Control Flow Components
- Data Flow Components
- Package Configurations
- Error Handling and Logging
- Deploying SSIS packages
- Different ways of Executing SSIS packages and many more...