SSIS 1 - Introduction to SSIS

In last project, we created how to extract, transform and load data from OLTP to OLAP(Data warehouse) using SQL script.

Everything went well, however, in most of the real world scenario, these scripts could get very complicated. You might need to collect data from multiple heterogeneous data sources. These sources might need multiple layer of transformation. This may not sound too bad to you but trust me, when you get to maintain someone else's script, it would a big pain for you.

And for exactly these type of scenarios, Microsoft provides a service to visually maintain the flows and procedure.

For more information , you can see:
SQL Server Integration Services: https://aka.ms/edx-dat217x-ssis

To Set up your SSIS tool, you will have to download and install SQL Server Data Tool.
Now, if you are using SQL server 2016 or above then you will have to use SQL Server Data Tool 2015.

For Installation please read https://docs.microsoft.com/en-us/azure/sql-data-warehouse/sql-data-warehouse-install-visual-studio

Once Installed, you should be able to find this on your system

Launch the application

File > New > Project

Expand Installed >  Templates > Business Intelligence > Integration Service > Integration Services Project

If you do not see Business Intelligence, then you might have to reinstall your SQL server and SSDT to include these services. 

if you are confused with Project and Solution then think of solution as a main folder that can have more than one project and each project here would perform one set of ETL scripts and codes

click Ok.

And Now you are ready to start with you SSIS Project.

You should have the highlighted frames in your view.

Following is a content from Edx course at DAT217x
The Control Flow Tab

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:

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

Working with the control flow tab

The Data Flow Tab

Data flows are the only task that have their own tab.

Data flow tasks encapsulate the data flow engine, and are specialized for transferring data from one location to another. 


Working with the Data Flow tab