SQL Server Integration Services — SSIS

Photo by Sigmund on Unsplash

In this story, we’ll look at Microsoft SQL Server Integration Services — SSIS — a component of Business Intelligence (BI), why it’s used, and then look at the components of SSIS, and its use in a sample project.

Learning Objectives and Overview
In this presentation, we’ll briefly seek to :

• Understand and introduce SQL Server Integration Service
• Understand and enumerate the components of an SSIS package
• Highlight similarities of SSIS package components to a software program,
• Review features and benefits of SSIS and
• Look at a sample Integration Services project in BIDS.

SQL Server Integration Services- Why?

BIDS — Business Intelligence Decision Support — the making of good decisions — in an organization from relevant and accurate data which is contained in a Data Warehouse. Disparate source systems (HR, Operations, Flat files, Engineering) feed data to a data warehouse, a central repository with all company or organization-wide information.

Why a central repository?

1. Single Version of truth — it serves as the single-source of truth. The main record or parent record is stored here.
2. Performance — data warehouses are a special type of database called analytical databases (OLAP) as compared to traditional databases (OLTP).

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 is basically a platform for making programs which collect and move data from a source to a destination, and in the process of that move, it also transforms the data into a shape that is needed.

This process is collectively known as ETL : Extraction, Transform, Load.

Everything in SSIS revolves around Packages.

ETL process

SSIS — part of BI

• SSIS is part of BI — Business Intelligence (data conversion into information for business decisions).

BI = $

Why? Business Use Cases

Data sources are often located in disparate and distributed data sources in various or different formats. SSIS is a platform used to collect and move data (E) and transform (T) it along the way, and loaded (L) into a Data Warehouse.

ETL overview

SSIS Features

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;
• Management service, the Integration Services service for administering package execution and storage;
• Application programming interfaces (APIs) for programming the Integration Services object model.

Benefits of SSIS

• To solve complex business problems utilizing data warehousing
• Data extraction and transformation from disparate sources such as XML data files, FTP, flat files and relational data sources.
• Resource management — Pulls varied data from many data sources. SSIS does not require an instance of SQL Server to run, so processing can be moved to a machine other than SQL Server.
• Data Access — SSIS can consume data from sources that can’t be accessed directly using T-SQL, e.g. FTP, HTTP, MSMQ, Analysis Services and SMO
• Cost effective — transform data without a staging area.
• SSIS provides transform functionality not available in T-SQL, e.g. Fuzzy logic, data profiling (SSIS 2K8), data and text mining and direct insert to SSAS.
• GUI SSIS designer — a visual representation of the work being done.
• Error handling, logging and auditing — SSIS provides a more sophisticated structured error handling system as well as multiple options for logging and auditing.
• SSIS can use .NET Data providers as an alternative to the OLEDB Providers allowing access to systems such as SAP and Oracle.
• SSIS allows parallel execution of data flows.
• Complex transformations, multi-step operations, operations requiring a lot of programmatic work, aggregating data from different data sources or types, and structured exception handling.
• Debugging is simplified because package logic is all in one place.
• “Bad” data can be redirected to a different data sources for further examination or processing.

Architecture of SSIS

Since everything in SSIS revolves around Packages, we need to understand what is a package? It’s a unit of execution which is run to move and transform data. A package is a file similar in concept to an .EXE file. In SSIS, you can Create, Deploy and Run packages to move and transform data.

Components of SSIS Package

1. Control Flow

• Containers — provide structures in packages
• Tasks — provide functionality
• Precedence constraints — connect the executables, containers and tasks into an ordered control flow

2. Data Flows

3. Data Sources (source and destination)

4. Deployment

That’s it folks. A brief summary of SSIS. If I continue on, it will turn into a term paper.