ETL Processing with SQL Server Integration Services (SSIS)

In previous post, ETL process was performed with SQL programming.
To achieve the same result, one can also use SQL Server Integration Services(SSIS).

What is SSIS?
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.

ETL process in SSIS. The green check mark indicates that the package execution completed with success.

The primary runtime engines of SSIS are control flow and data flow.

Control Flow: The control flow is created by dragging sequence containers and control flow tasks from the SSIS toolbox onto the designer surface. The Control Flow tab, as the name implies, lets you control the flow of your data.

The most common control flow tasks are as follows:

• Annotations: Text blocks that contain notes or explanations. 
• Data Flow Task: Moves data between sources and destinations. 
• Execute SQL Task: Runs the statement or stored procedure. 
• Sequence container: Groups tasks together.

Control flow Page

Data flow: Data flow, as the name implies, is an SSIS task in which data flows from at least one source component to at least one destination component. Data flows are made up of three individual components:

• Sources: extract data from various data stores
• Transformations: modify data the data
• Destinations: load data, or create in-memory data sets

Data Flow tasks can be found in the Control Flow Toolbox, and placed onto the designer surface. They are then configured by right-clicking the task and selecting Edit from the context menu, or selecting the Data Flow tab at the top of the package designer window.

Adding Sources and Destinations within Data Flow Task