SQL Server integration services

SQL Server integration services (SSIS) are used as a data pump of SQL Server. SSIS is used to maintain data movements and transformations between a wide scale of heterogeneous data sources and destinations as well as migrating or transforming data between several instances of SQL Server. A very common use case is using SSIS in data warehousing to extract, transform, and save data from online transactional processing (OLTP) databases to a data warehouse.

The working unit of this technology is the SSIS package. It's a runnable unit of integration services and we can think about it as one simple program. Its definition consists of the so called Control Flow task set such as creating a temporary folder, if it succeeds in downloading CSV files from some FTP site, and so on. One of the most crucial tasks in control flow is called the Data Flow task. This Data Flow task causes the execution of the SSIS package to switch to a Data Flow transform set.

The integration service itself is not mandatory for SSIS package execution but the service is used for integration services packages management. It's installed for backward compatibility with older versions of SSIS packages deployment model. SSIS packages are now commonly placed into a database called SSISDB. The database is not often accessed directly by users or administrators; it is maintained using integration services catalogs part of management studio.

From an administrator's point of view, the SSIS service installation could be omitted if all existing SSIS packages can be deployed to the integration services catalog, which can be created anytime just by a few clicks in the Management Studio.

Integration services often cooperate with two features for data cleansing, validating, and deduplicating. These services are called Master Data Services and Data Quality Services.