Getting started with SSIS

What is ‘SSIS’:

• ·         SQL Server integration services (SSIS) is a tool used to Extract, Transform and Load (ETL) data in different databases.
• ·         SSIS is a Microsoft product lunch with SQL Server 2005. Prior to this it was known as DTS services.

Pre-requisite:

• ·         Install SQL Server 2005 or any other version later to it. This will automatically install SSIS Engine to your system.
• ·         Open SQL Server Business Intelligence Development studio (BIDS) to create SSIS package. 

Getting Started:

• ·         To create a SSIS package first a Solution and a SSIS Project need to be created.
• ·         A solution is a collection of different types of project like: SSIS project, SSMS project, SSAS project and different C# projects.
• ·         A SSIS Project is a collection of SSIS packages and other files related to the project.
• ·         To create a solution go to File->New->Project
• ·         Click on Integration Services Project.

• ·         Fill the Name and location. By default the solution name will be the Name of the project. If you want you can customize the name of the solution. Then press OK. 
• 
  
• ·         Once you click OK a solution and a project will be created along with a default package.

Getting familiar with BIDS:

• ·         The following screen will appear once you click OK.

• ·         The following panes are present inside the BIDS:

o   Solution Explorer

o   Properties

o   Toolbox

o   Variables

o   Package

§  Control Flow

§  Data Flow

§  Event Handlers

§  Package Explorer

§  Connection Manager

Solution Explorer:

·         Shows all the Data Sources, Data views, Packages and files preset inside the solution.

Properties:

·         Shows the property of the selected component.

Toolbox:

·         Contains different tasks when the Control flow tab is open.

·         Contains different transformations when the Data flow tab is open.

Variables:

·         Contains all the user defied and system defined variables present inside the package.

Package:

·         Package is a collection of task that is to be performed to do the ETL.

·         It’s a XML file containing all the ETL instructions inside the XML tags and saved with .dtsx extension.

There are four tabs present inside the package area:

Control Flow:

·         Contains all tasks present inside a package.

·         Task is the granular level of work that a package performs. For example: Execute SQL Task, Data Flow task, Send mail task

Data Flow:

·         Data flow contains source where the data need to be extracted, transformations which modifies data according to the business requirement and target where the transformed data going to reside.

Event Handler:

·         Event handler also contains task like the control flow. The only difference is the tasks will trigger only when the associated event happens.

·         The events can be associated on the package level, container level or on the task level.

Package Explorer

·         Contains the hierarchical structure of all the components present inside a package.