How Is Ssis Runtime Engine Different From The Ssis Dataflow Pipeline Engine?
The SSIS Runtime Engine manages the workflow of the packages during runtime, which means its role is to execute the tasks in a defined sequence. As you know, you can define the sequence using precedence constraints. This engine is also responsible for providing support for event logging, breakpoints in the BIDS designer, package configuration, transactions and connections. The SSIS Runtime engine has been designed to support concurrent/parallel execution of tasks in the package.
The Dataflow Pipeline Engine is responsible for executing the data flow tasks of the package. It creates a dataflow pipeline by allocating in-memory structure for storing data in-transit. This means, the engine pulls data from source, stores it in memory, executes the required transformation in the data stored in memory and finally loads the data to the destination. Like the SSIS runtime engine, the Dataflow pipeline has been designed to do its work in parallel by creating multiple threads and enabling them to run multiple execution trees/units in parallel.
What Is A Task?
A task is very much like a method of any programming language which represents or carries out an individual unit of work. There are broadly two categories of tasks in SSIS, Control Flow tasks and Database Maintenance tasks. All Control Flow tasks are operational in nature except Data Flow tasks. Although there are around 30 control flow tasks which you can use in your package you can also develop your own custom tasks with your choice of .NET programming language.
The SSIS Runtime Engine manages the workflow of the packages during runtime, which means its role is to execute the tasks in a defined sequence. As you know, you can define the sequence using precedence constraints. This engine is also responsible for providing support for event logging, breakpoints in the BIDS designer, package configuration, transactions and connections. The SSIS Runtime engine has been designed to support concurrent/parallel execution of tasks in the package.
The Dataflow Pipeline Engine is responsible for executing the data flow tasks of the package. It creates a dataflow pipeline by allocating in-memory structure for storing data in-transit. This means, the engine pulls data from source, stores it in memory, executes the required transformation in the data stored in memory and finally loads the data to the destination. Like the SSIS runtime engine, the Dataflow pipeline has been designed to do its work in parallel by creating multiple threads and enabling them to run multiple execution trees/units in parallel.
What Is A Task?
A task is very much like a method of any programming language which represents or carries out an individual unit of work. There are broadly two categories of tasks in SSIS, Control Flow tasks and Database Maintenance tasks. All Control Flow tasks are operational in nature except Data Flow tasks. Although there are around 30 control flow tasks which you can use in your package you can also develop your own custom tasks with your choice of .NET programming language.
What Is A Precedence Constraint And What Types Of Precedence Constraint Are There?
SSIS allows you to place as many as tasks you want to be placed in control flow. You can connect all these tasks using connectors called Precedence Constraints. Precedence Constraints allow you to define the logical sequence of tasks in the order they should be executed. You can also specify a condition to be evaluated before the next task in the flow is executed.
These are the types of precedence constraints and the condition could be either a constraint, an expression or both Success (next task will be executed only when the last task completed successfully) or Failure (next task will be executed only when the last task failed) or Complete (next task will be executed no matter the last task was completed or failed).
 |
| Mindtree Frequently Asked SSIS Interview Questions Answers |
What Is A Container And How Many Types Of Containers Are There?
A container is a logical grouping of tasks which allows you to manage the scope of the tasks together.
These are the types of containers in SSIS
Sequence Container - Used for grouping logically related tasks together
For Loop Container - Used when you want to have repeating flow in package
For Each Loop Container - Used for enumerating each object in a collection; for example a record set or a list of files.
Apart from the above mentioned containers, there is one more container called the Task Host Container which is not visible from the IDE, but every task is contained in it (the default container for all the tasks).
What Are Variables And What Is Variable Scope?
A variable is used to store values. There are basically two types of variables, System Variable (like ErrorCode, ErrorDescription, PackageName etc) whose values you can use but cannot change and User Variable which you create, assign values and read as needed. A variable can hold a value of the data type you have chosen when you defined the variable.
Variables can have a different scope depending on where it was defined. For example you can have package level variables which are accessible to all the tasks in the package and there could also be container level variables which are accessible only to those tasks that are within the container.
What Is An Ssis Proxy Account And Why Would You Create It?
When we try to execute an SSIS package from a SQL Server Agent Job it fails with the message "Non-SysAdmins have been denied permission to run DTS Execution job steps without a proxy account". This error message is generated if the account under which SQL Server Agent Service is running and the job owner is not a sysadmin on the instance or the job step is not set to run under a proxy account associated with the SSIS subsystem. Refer to this tip to learn more about it.
Explain what is a checkpoint in SSIS?
Checkpoint in SSIS allows the project to restart from the point of failure. Checkpoint file stores the information about the package execution, if the package run successfully the checkpoint file is deleted or else it will restart from the point of failure.
For what data checkpoint data is not saved?
Checkpoint data is not saved for ForEach Loop and ForLoop containers.
Explain what is conditional split transactions in SSIS?
Conditional split transformation in SSIS is just like IF condition, which checks for the given condition based on the condition evaluation.
List out the different types of Data viewers in SSIS?
Different types of data viewers in SSIS include
Grid
Histogram
Scatter Plot
Column Chart
Explain what is the role of Event Handlers tab in SSIS?
On the event handlers tab, workflows can be configured to respond to package events. For instance, you can configure workflow when any task stops, fails or starts.
Explain how you can notify the staff members about package failure?
Either inside the package you could add a Send Mail Task in the event handlers, or you can even set notification in the SQL Agent when the package runs.
Explain how can you do an incremental load?
The best and fastest way to do incremental load is by using Timestamp column in the source table and storing the last ETL timestamp.
Explain how would you do logging in SSIS?
Logging in SSIS can be done by logging various events like onError, onWarning, etc. to the various options like a flat file, XML, SQL server table, etc.
Mention how would you deploy an SSIS package on production?
To deploy SSIS package we need to execute the manifest files and need to determine whether to deploy this into File System or onto SQL Server. Alternatively you can also import package from SSMS from SQL Server or File System.
Explain how to handle Early Arriving Facts or Late Arriving Dimension?
Late Arriving Dimension are unavoidable, to handle these we can create a dummy dimensions with natural/business key and keep the rest of the attributes as null or default. So when actual dimension arrives, the dummy dimension is updated with Type 1 change. This is also referred as Inferred Dimensions.
Explain What Is Ssis?
SSIS or SQL Server Integration Services (SSIS) is a component of Microsoft SQL Server, which can be used to accomplish a broad range of data migration tasks.
Explain What Is A Checkpoint In Ssis?
Checkpoint in SSIS allows the project to restart from the point of failure. Checkpoint file stores the information about the package execution, if the package run successfully the checkpoint file is deleted or else it will restart from the point of failure.
Explain What Is Connection Managers In Ssis?
While gathering data from different sources and writing it to a destination, connection managers are helpful. Connection manager facilitates the connection to the system that include information’s like data provider information, server name, authentication mechanism, database name, etc.
Explain What Is Ssis Breakpoint?
A breakpoint enables you to pause the execution of the package in business intelligence development studio during troubleshooting or development of an SSIS package.
Explain What Is Event Logging In Ssis?
In SSIS, event logging allows you to select any specific event of a task or a package to be logged. It is very helpful when you are troubleshooting your package to understand the performance package.
Explain What Is Logging Mode Property?
SSIS packages and all the associated tasks have a property called LoggingMode. This property accepts three possible values
Disabled: To enable logging of the component
Enabled: To disable logging of the component
UseParentSetting: To use parent’s setting of the component
Explain What Is A Data Flow Buffer?
SSIS operates using buffers; it is a kind of an in-memory virtual table to hold data.
For What Data Checkpoint Data Is Not Saved?
Checkpoint data is not saved for ForEach Loop and ForLoop containers.
Mention What Are The Important Components Of Ssis Package?
The important component in SSIS package are
Data flow
Control flow
Package explorer
Event handler
Explain What Is Solution Explorer In Ssis?
Solution Explorer in SSIS Designer is a screen where you can view and access all the data sources, data sources views, projects, and other miscellaneous files.
Explain What Does It Mean By Data Flow In Ssis?
Data flow in SSIS is nothing but the flow of data from the corresponding sources to the target destinations.
Define What Is “task” In Ssis?
Task in SSIS is a very much similar to the method of any programming language that represents or carries out an individual unit of work. Tasks are categorized into two categories
Control Flow Tasks
Database Maintenance Tasks
Explain What Is Ssis Package?
A package in SSIS is an organized collection of connections like data flow elements, control events, event handlers, parameters, variables, and configurations. You assemble them by either building it programmatically or by graphical design tools that SSIS provides.
Explain What Is A Container? How Many Types Of Containers Are There In Ssis?
In SSIS, a container is a logical grouping of tasks, and it allows to manage the scope of a task together.
Types of containers in SSIS are
Sequence container
For loop container
Foreach loop container
Task host container
Explain What Is Precedence Constraint In Ssis?
Precedence Constraint in SSIS enables you to define the logical sequence of tasks in the order they should be executed. You can connect all the tasks using connectors- Precedence Constraints.
Explain What Variables In Ssis And What Are The Types Of Variables In Ssis?
Variable in SSIS is basically used to store values. In SSIS, there are two types of variables system variable and user variable.