SSIS Architecture

Part 1- Various modes of accessing/executing ETL. E.g. Command Line, SSIS Designer etc.

 

Part 2- It monitors the running of the ETL’s and manages the storage of ETL’s.

 

Part 3- Storage mechanism by which the ETL’s can be stored. There are two major ways of storing(Deploying) the ETL’s : a) In the file system b) In SQL Server MSDB database.

 

Part 4- Used to loop through multiple objects of same kind. E.g. Process multiple files in the same folder OR Iterate through all records in a table.

 

Part 5- Provides(Stores) the physical connectivity information to source and destination. In simple words this object stores the Connection string.

 

Part 6- Provides the option to take some action (handle the event) based on certain events. For e.g. we want to be intimated through an email when ETL fails. So here failure of the ETL can be one event and    notifying certain group through an email is the action.

 

Part 7- Used to move data from a particular source to a particular destination while performing transformations. This is the most important task in SSIS.

 

Part 8-  Various Source of the data(Self Explanatory). It can be in any form like .txt file, Excel file, OLEDB database.

 

Part 9- Used to audit the ETL. For e.g. tacking down all the information like user who executed the ETL, Time of Execution, Error Description etc.

 

Part 10- Task is the unit of work that is that is performed by the ETL. It can be as simple as moving a group of file from one location to other.

 

Part 11- This complete section controls the actual flow of actions to be performed in the ETL process.

 

 

In the coming articles we will be looking to each and every part in more detail and also how to implement/configure them in SQL Server Integration Services.