Microsoft Integration: An SSIS Introduction

 SSIS Introduction


SSIS:  SQL Server Integration service is used for the ETL process, which can be used for data migration task.

The ETL process is divided into 3 parts:

  1. Data Extraction.

  2. Data Transformation.

  3. Data loading.



etl-process.jpg

                                                                                           Figure 1: ETL process

Main terms of SSIS are data source, conversion and data destination.

Data Source: a data source is a database where raw data or initial data is stored like SQL Server database, Oracle database, Excel file and Flat file or CSV files.

Transformation tool: After importing data in SSIS, data needs to be transformed into a desirable format. To obtain that desirable format some data transformation tools are used.  

Data Destination: After conversion process data is exported to a desirable destination. The destination can be SQL Server database, Flat file, Excel and also an oracle.



SSIS Environment has four important windows 

  1. Solution explorer.

  2. SSIS tools box.

  3. Properties. 

  4. Package design


Solution Explorer: This window is for making a package and setting up a connection for that package

  • Connection manager

  • SSIS Packages

  • Miscellaneous


SSIS Tool Box: This window has all the tool and function which are used in SSIS ETL process. Features for this tools box different for Data Flow and Control Flow.

  Control flow:

  • Favourites:

  • Data flow task:

  • Execute SQL task

  • Common:

  • Analysis Service process task

  • Bulk Insert task

  • Execute package task

  • Execute process task

  • Expression task

  • FTP task

  • Script task and etc.

  • Containers:

  • For loop container

  • Foreach loop container

  • Sequence Container

  • Other tasks:

  • Analysis service execute the task

  • Backup database task

  • CDC control task

  • Data mining query task

  • Execute SQL server agent task

  • History cleanup task

And etc.

   Data Flow:

  • Favourites:

  • Destination assistant 

  • Source assistant

  • Common:

  • Aggregate

  • Data split

  • Conversion

  • Lookup

  • Merge

  • Union

  • Sort

  • Script component

  • Multicast

  • OLE DB commands and etc


  • Other transforms:

  • Audit

  • CDC Split

  • Cache transform

  • Data mining query

  • DQS Cleansing

  • Copy column

  • Export column

  • Import column

  • Fuzzy grouping

  • Fuzzy lookup

  • Pivot

  • Unpivot

  • Row sampling

  • Percentage sampling

  • Term extraction 

  • Term lookup



  • Other sources:

  • ADO NET Source

  • CDC Source

  • Excel Source

  • Flat file source

  • ODBC Source

  • OLE DB Source

  • Raw File source

  • XML Source

  • Other destination:

  • ADO NET Destination

  • Data mining model training

  • Datareader destination

  • Dimension processing

  • Excel destination

  • ODBC destination

  • OLE DB destination

  • Partition processing

  • Raw file destination

  •  Recordest destination

  • SQL server compact destination

  • SQL sever destination


Properties window: Properties window giver every detail of any tool feature of package object of our project. Properties window can be obtained by select any object of package and click F4 button.

           

Package design: Package design window holds SSIS model detail and contains all feature of package project. Package design architecture includes the below components.

  • Control Follow: It controls data flow in our package.

  • Data follow:  A data follow controls follow of data in the package and decide executable operation on data

  • Parameters: parameters are used in the package for using an inner or outer value in multiple packages.

  • Event handlers: As the name say event handler handle event in data flow and does a particular task as it trained to do so.

  • Package explorer: 

  • Execution result: This will show the result of a package and if there is any error then it would be indicated with proper error cause and code.

    

These are all basic detail about SSIS tool.

Note: this blog post is not an older version of SSIS, now we have many Location and data operations available.

         

Comments

Post a Comment