SSIS Architecture in MSBI

SSIS stands for SQL Server Integration Service. SSIS helps in executing, a large range of data movement tasks. It is an element in Microsoft SQL Server database software.

It is a quick and adaptable data warehousing tool, that is used for getting information, filling information and changes like cleaning, collection and combining information, etc. By this, it is simple to move information from one database to another database. It can take out information from various big sources, such as SQL Server databases, Oracle, Excel files, and DB2 databases, etc.

It has graphical tools and wizards to do workflow tasks like composing email messages, FTP functions, information sources, and destinations. you can learn more through msbi online training

Control Flow

Control flow is very important in the SSIS package, as it acts as the brain in the SSIS package. It helps us to organize the execution sequence for all elements. The elements have containers, and functions that are maintained by precedence constraints.

Sources- It is an element that is added to the Data Flow design, to define the location of source data.

Destinations- In the Data Flow, destinations take the information when the data pipe goes out of the final transformation element.

Precedence Constraints:

These are package elements that route functions to execute, in an already defined sequence. It also mentions the workflow of the complete SSIS package. It manages the execution of two connected functions by executing the destination tasks, depending on the output of the previous task.

Task

It is a single unit of work. It is similar to the function/method implemented in a coding language. Anyway, in the SSIS Architecture of MSBI, instead of using programming methods, we use drag and drop methods to create a surface and to arrange them.

Containers – These are important units used for assembling tasks collectively, into units of work. It helps to declare variables and event handlers in the range of certain containers. It also offers visual consistency. There are four types of containers. learn more through msbi training

A sequence container – It makes us arrange secondary tasks, by categorizing them, and makes us use transactions or give logging to the container.

for loop container- Its function is the same as the sequence container, except that it makes us run the tasks many times. Anyway, it depends on an estimation condition, such as looping from 1 to 100.

For each loop container- In this, we can do looping. For each loop container, instead of using a condition expression, loops are performed with a group of objects like documents in a folder.

Data Flow

The SSIS tool is mainly used to take out information into the server’s memory, change it, and record it to another destination. It acts as the heart of the SSIS Architecture of MSBI.

Event Handler- These run in reaction to the run-time events

Package

A package is a group of tasks that execute in a sequence manner. The sequence in which the task will execute is maintained by the precedent constraints.

A package is used to store files in SQL Server, in the msdb or package catalog database. It saves as .dtsx file, which is an organized file same as .rdl files which are for Reporting services.

Connection Managers- These are used to assemble connection strings, to data sources and to summarize them from the SSIS packages. Different tasks can use the same connection manager.

Parameters- Parameters act like variables but, it has some important exceptions. It can be arranged from external to the package, in a simple way. It can be called as values, that have to be given in for the package to begin.

Transformations- These are important elements in the Data Flow, that makes changes to the information in the data pipeline.

Log Providers

It records the package run-time information like start time and end time of the package and its functions and containers.

Package Configurations- When package development is completed and before arranging the package, in the production domain form UAT, we have to do specific package configurations, according to the production server.

Variables- Variables are given to calculate expression, while execution.

Parameters- Parameters act as variables, but with some important exceptions.

In this article, I have shared about SSIS Architecture in MSBI. Follow my articles to get more updates on MSBI. learn through msbi online training India professionals