SQL Server Integration Services — SSIS
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?
- Single Version of truth — it serves as the single-source of truth. The main record or parent record is stored here.
- 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.
SSIS — part of BI
- SSIS is part of BI — Business Intelligence (data conversion into information for business decisions).
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.
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
- 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.