SSIS Tutorial

SQL Server Integration Services (SSIS)

The SQL Server Integration Services (SSIS) is a powerful ETL tool. This tool is used for building enterprise-level data transformation and data integration solutions.

SSIS provides the ability to:

• retrieve data from any source
• load data into any source
• define a workflow
• perform various transformations on the data: e.g., convert data from one type to another, perform calculations, etc.

It performs three important things. Extraction, Transformation,  and  Loading.

1. 1. Extraction (E):  Collection of data from different sources.
2. 2. Transformation (T):  A different form of data obtained from different sources and it converted according to business needs.
3. 3. Loading  (L):  Data warehouse contains the loaded data.

Why do we use SSIS tools?

There are the following reasons to use the SSIS tool:

• It contains a GUI (Graphical User Interface) that help users to transform data easily rather than writing the large programs.
• It includes many in-built features and transformation to solve the complex business problems by building high-performance data integration packages.
• It can easily move millions of data from one data source to another in few minutes.
•  It includes graphical tools & wizards for performing workflow functions like FTP operations, Sending emails, data source, and destination.
• It helps us to merge data from various data stores.
• It helps users in identifying, capturing, or analyzing the data.                
• It provides an advanced level of structured error handling.
• It is cheaper than other ETL Tools.
• It provides tight integration with other products of Microsoft.

Architecture of SSIS tool

SSIS Architecture consists of the following parts:

Packages

A package is a collection of control flow elements, data flow elements, variables, event handlers, and connection managers. Initially, when you create a package, it is an empty object that does nothing. But when you create the basic package, you can add advanced features such as log providers and variables to extend package functionality in the package.

Control flow

A control flow contains one or more containers and tasks, and they execute when the package runs.

Data flow

A data flow contains the source and destination which are used to modify and extend data, extract and load data, and the paths that link sources, transformations, and destinations. A data flow task is executable within the SSIS package that creates, runs, and orders the data flow.

Connection managers (connections)

A connection manager is a link between the package and the data source. It defines the connection string for accessing the data. The package includes at least one connection manager.

Event Handlers

The event handler is a workflow that runs in response to the run-time events raised by a package, container, or task.

Log Providers and logging

The log is a collection of information about the package that is collected when the package runs.

Variables

Variables are used to evaluate an expression at the runtime.

Integration Services supports the two types of variables –

1. 1. System variable – A system variable provides useful information about the package at run time.
2. 2. User-defined variable – A user-defined variable supports a custom scenario in the package.

Tasks        

A task can be explained as an individual unit of the work. You can write custom tasks using the programming language that supports COM, such as Visual Basic, C#, or a .NET programming language.

Precedence Constraints

Precedence constraints are the arrows in a Control flow of a package component that direct tasks to execute in a predefined order and manage the order in which the tasks will execute.

Transformations

Transformations are the key components within the Data Flow that allow changes to the data within the data pipeline.

Containers

Container is the core unit in the SSIS architecture for grouping tasks together logically into units of work. It allows us to declare variables and event handlers.

There are the following types of containers in SSIS:

• Sequence Container
• For loop Container
• Foreach  loop container

Destinations

SSIS destination is used to load data into a variety of database tables/views/SQL commands. The destination editor provides an ability to create a new table.

Components of SSIS

SSIS consists of three major components –

1. 1. Operational Data – Operational Data is a database designed to integrate the data from multiple sources and also performs the operation on the data. It is the place where most of the data is used in the current operation.
2. 2. ETL – ETL stands for Extract Transform and Load. It is the process of extracting the data from various sources, transforming this data to meet the requirement and then loading into a target data warehouse.
3. 3. DataWarehouse – Data Warehouse is used for assembling and managing data from various sources for the purpose of answering business questions. Hence, it helps in marketing decisions.

Advantages of SSIS tool

There are the following advantages of the SSIS tool –

• Tight Integration with the other Microsoft SQL family.
• Better for the multi-step operations, complex transformations
• Aggregating data from different data sources and providing structured exception handling.
• Easier to maintain and load package configuration.
• Can handle data that occurs from heterogeneous data sources at the same package.

Disadvantages of SSIS

There are the following disadvantages of the SSIS tool –

• SSIS sometimes creates issues in non-windows environments.
• Vision and strategy are not clearly defined.
• It requires high memory and conflicts with SQL.
• In case of CPU allocation, it is a problematic case when you have more packages to run parallel.

General Skills Required to Become an SSIS Developer

Scripting Language

There will be times where the ETL instruments can’t do everything expected to finish the necessities, so ETL Developers regularly need to get their hands messy in the frameworks they are working with. Learning a scripting language can help engineers with shuffling records, catalogs, clients, and authorization that can confuse ETL. Prominent scripting dialects for ETL incorporate Python, Perl, and Bash. 

Organization Skills

As an SSIS Developer, you will have a ton of assignments to finish in a venture or dash. For any engineer, having the option to keep the current work sorted out and organized is constantly tremendous in addition to. This is critical for SSIS Developers who not just need to organize the jobs needing to be done yet, besides, the ETL mappings and work processes that you make. To have the option to keep things composed and organized will help with the investigating procedure, numerous organizations will as of now have some association principles. For those that don’t, setting up your very own benchmarks will be a basic piece of support and reliable advancement.  

Creativity

In all honesty, having an imagination is a gigantic favorable position as a designer. For instance, as an SSIS Developer, you will be at times given an STTM (Source to Target Mapping) record, which diagrams precisely what you’ll have to do. Be that as it may, you might not have this record and should make the mappings without any preparation. Like a craftsman with a clear canvas, having the option to utilize various ways requires a considerable amount of imagination: breaking new ground and having thoughts that different engineers may not consider makes the activity simpler, and increasingly fun!  

Problem Solving

In SSIS Development, nothing ever truly works out as expected on the first occasion when you run an occupation or make an update to a current mapping. At the point when you are on a creation bolster job, the business/customer will need the issue settled in a certain period as the business relies upon these employments to work appropriately. To be a powerful issue solver, the remainder of the aptitudes on this rundown has an impact: having a sorted out standard and commonality with your ETL apparatus can streamline the critical thinking process.