How to read data from multiple Excel worksheets with SQL Server Integration Services

https://www.mssqltips.com/sqlservertip/4157/how-to-read-data-from-multiple-excel-worksheets-with-sql-server-integration-services/

How to Get Date Like 2016092210(yyyymmddhh) by using Expression Task

SUBSTRING(REPLACE(REPLACE(REPLACE(REPLACE((DT_WSTR, 30) GETDATE(),"-",""),":",""),".","")," ",""),1,14)

INTRODUCTION TO SSIS

Sql Server Integration Services (SSIS) is ETL (Extract, Transform, Load) Tool, developed by Microsoft to perform ETL operations; i.e. Extract, Transform, and consolidate data from different sources and load it to a single or multiple destinations. It provides a platform to build data integration and workflow applications. SSIS packages can be created using BIDS (Business Intelligence Development Studio).

Business Intelligence Development Studio is the environment that we will use to develop packages for data extraction, transformation, and loading (ETL) in Integration Services. Business Intelligence Development Studio is the Visual Studio 2008 environment with enhancements that are specific to business intelligence solutions. Business Intelligence Development Studio (BIDS) will be available as client tool when installing SQL Server Management Studio (SSMS).

Usage of SSIS:

Populate data warehouses.

• Manage, Clean and Standardize data.

• Merge data from multiple data sources, either from relational data bases or from flat files etc.

• Automating database administrative tasks.

SSIS Architecture

Microsoft SQL Server Integration Services (SSIS) consist of four key parts:

• SSIS Service
• SSIS Object Model
• SSIS runtime engine and the runtime executables
• SSIS dataflow engine 

Integration Services Service:

• Monitors running Integration Services packages and manages the storage of packages
• Integration Services object model
• Includes native and managed application programming interfaces (API) for accessing
• Integration Services tools, command-line utilities, and custom applications

Data Transformation Run-Time Engine:

•      Handles control flow of the package   
•      Support Logging,Debugging ,Config,Connections & transactions.
•      Package,containers,Tasks and Event handles.

Data Flow Engine(ETL):

•    Also called as Data Transformation Pipe Line Engine
•    Data sources or Data Adapters 
•    Transformations 
•    Destination Targets or Destination Adapters 

Please check below SSIS Architecture Diagram