What is the SSIS package and what does it do?

Can someone tell me what the SSIS package is all about?

I am totally new to data-warehousing and I am going crazy with the terms SSIS, DTL, ETL and so on. I am looking for a big picture and how it fits in the overall .NET/SQL Server architecture.

For Latest Info About SSIS > https://docs.microsoft.com/en-us/sql/integration-services/sql-server-integration-services

From the above referenced site:

> Microsoft Integration Services is a platform for building > enterprise-level data integration and data transformations solutions. > Use Integration Services to solve complex business problems by copying > or downloading files, loading data warehouses, cleansing and mining > data, and managing SQL Server objects and data. > > Integration Services can extract and transform data from a wide > variety of sources such as XML data files, flat files, and relational > data sources, and then load the data into one or more destinations. > > Integration Services includes a rich set of built-in tasks and > transformations, graphical tools for building packages, and the > Integration Services Catalog database, where you store, run, and > manage packages. > > You can use the graphical Integration Services tools to create > solutions without writing a single line of code. You can also program > the extensive Integration Services object model to create packages > programmatically and code custom tasks and other package objects. >

Getting Started with SSIS - http://msdn.microsoft.com/en-us/sqlserver/bb671393.aspx

If you are Integration Services Information Worker - http://msdn.microsoft.com/en-us/library/ms141667.aspx

If you are Integration Services Administrator - http://msdn.microsoft.com/en-us/library/ms137815.aspx

If you are Integration Services Developer - http://msdn.microsoft.com/en-us/library/ms137709.aspx

If you are Integration Services Architect - http://msdn.microsoft.com/en-us/library/ms142161.aspx

Overview of SSIS - http://msdn.microsoft.com/en-us/library/ms141263.aspx

Integration Services How-to Topics - http://msdn.microsoft.com/en-us/library/ms141767.aspx

SSIS (SQL Server Integration Services) is an upgrade of DTS (Data Transformation Services), which is a feature of the previous version of SQL Server. SSIS packages can be created in BIDS (Business Intelligence Development Studio). These can be used to merge data from heterogeneous data sources into SQL Server. They can also be used to populate data warehouses, to clean and standardize data, and to automate administrative tasks.

SQL Server Integration Services (SSIS) is a component of Microsoft SQL Server 2005. It replaces Data Transformation Services, which has been a feature of SQL Server since Version 7.0. Unlike DTS, which was included in all versions, SSIS is only available in the "Standard" and "Enterprise" editions. Integration Services provides a platform to build data integration and workflow applications. The primary use for SSIS is data warehousing as the product features a fast and flexible tool for data extraction, transformation, and loading (ETL).). The tool may also be used to automate maintenance of SQL Server databases, update multidimensional cube data, and perform other functions.

Microsoft SQL Server Integration Services (SSIS) is a platform for building high-performance data integration solutions, including extraction, transformation, and load (ETL) packages for data warehousing. SSIS includes graphical tools and wizards for building and debugging packages; tasks for performing workflow functions such as FTP operations, executing SQL statements, and sending e-mail messages; data sources and destinations for extracting and loading data; transformations for cleaning, aggregating, merging, and copying data; a management database, SSISDB, for administering package execution and storage; and application programming interfaces (APIs) for programming the Integration Services object model.

As per Microsoft, the main uses of SSIS Package are:

• Merging Data from Heterogeneous Data Stores Populating Data

• Warehouses and Data Marts Cleaning and Standardizing Data Building

• Business Intelligence into a Data Transformation Process Automating

• Administrative Functions and Data Loading

For developers:

SSIS Package can be integrated with VS development environment for building Business Intelligence solutions. Business Intelligence Development Studio is the Visual Studio environment with enhancements that are specific to business intelligence solutions. It work with 32-bit development environment only.

Download SSDT tools for Visual Studio:

http://www.microsoft.com/en-us/download/details.aspx?id=36843

Creating SSIS ETL Package - Basics :

https://docs.microsoft.com/en-us/sql/integration-services/ssis-how-to-create-an-etl-package?view=sql-server-2017

Sample project of SSIS features in 6 lessons:

https://docs.microsoft.com/en-us/sql/integration-services/ssis-how-to-create-an-etl-package?view=sql-server-2017

> The SQL Server Integration Services, shortly called as SSIS is > a powerful ETL tool. SSIS ETL tool is used to extract data from > different sources and transform that Data as per user requirements and > Load data into various destinations. Remember, SSIS is the > second-largest tool to perform Extraction, Transformation, and Load > (ETL) operations. > > SSIS ETL tool helps to build enterprise-level data transformation and data integration solutions. This SSIS ETL tool is beneficial in > loading data from regular transactional data into Data Warehouse. So > that you can create reports using that data with the help of SSRS, > Tableau, etc. Apart from the Data Warehouse application, you can use > SSIS to work on typical data integration applications as well.

Steps in SSIS could be as:

SOURCE -> TRANSFORM -> DESTINATION

A simple interface of SSIS tasks that may succeed or fail in different scenarios:

For more information visit https://docs.microsoft.com/en-us/sql/integration-services/sql-server-integration-services?view=sql-server-ver15