SSIS | ETL : Create Integration Service Project and Package

Create ETL Package with SSIS to Extract, Transform, Load Data

Extract, transform, and load (ETL) is the process by which data is acquired from various sources. The data is collected in a standard location, cleaned, and processed. Ultimately, the data is loaded into a datastore from which it can be queried. Legacy ETL processes import data, clean it in place, and then store it in a relational data engine.

What you’ll learn

  • Create ETL Package.
  • Extract Data from data source.
  • Transform Data using Lookup transformations.
  • Add and Configure data source.
  • Add and configure destination connections.
  • Create Data Flows.
  • Execute Package.

Course Content

  • SQL Server Setup –> 8 lectures • 37min.
  • Visual Studio Setup –> 4 lectures • 22min.
  • Create a project and with SSIS –> 13 lectures • 1hr 10min.

SSIS | ETL : Create Integration Service Project and Package

Requirements

Extract, transform, and load (ETL) is the process by which data is acquired from various sources. The data is collected in a standard location, cleaned, and processed. Ultimately, the data is loaded into a datastore from which it can be queried. Legacy ETL processes import data, clean it in place, and then store it in a relational data engine.

“SQL Server 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.”

SSIS is part of the Microsoft SQL Server data software, used for many data migration tasks. It is basically an ETL tool that is part of Microsoft’s Business Intelligence Suite and is used mainly to achieve data integration. This platform is designed to solve issues related to data integration and workflow applications.

SSIS is commonly known as an “ETL” product, a means to move and transform data from one point to another from various data sources (SQL Server or Oracle databases, Excel, CSV or Text files and more). But SSIS is so much more. In its bare essence, SSIS is a workflow orchestrator: it executes different tasks, either sequentially or in parallel, and different execution paths can be executed, depending on success, failure or other factors including precedence constraints. This means SSIS can be used in other scenarios than just data warehousing with the corresponding SSAS and SSRS Microsoft Data Platform products. Database administrators can use it for example to schedule and execute backups of their databases.

SSIS is mainly a visual tool. Packages are developed in Visual Studio and they use a visual canvas to arrange tasks on the Control Flow tab. This means the solution is more easily understood by someone new to the project.