SSIS Interview Questions And Answers for beginner and experienced
- What is MSBI ?
MSBI stands for Microsoft Business Intelligence MSBI (Microsoft Business Intelligence) is one of the ETL tool related to the database side. MSBI is composed of tools which helps in providing best solutions for Business Intelligence and Data Mining Queries.
MSBI is divided into 3 categories:-
- SSIS – SQL Server Integration Services – Integration tool.
- SSAS – SQL Server Analytical Services -Analysis tool.
- SSRS – SQL Server Reporting Services – Reporting tool
- SSIS– This tool is used for the integration like duping the data from one database to another like from Oracle to SQL Server or from Excel to SQL Server etc.
This tool is also used for bulk transactions in the database like inserting lac's of records at once. We can create the integration services modules which will do the job foe us.
- SSAS– This tool is used to analyse the performance of the SQL server in terms of load balancing, heavy data, transaction etc. So it is more or less related to administration of the SQL Server using this tool. This is very powerful tool and through this we can analyse the data inserting in to the database like how many transactions happens in a second etc.
- SSRS– This tool is related to the generation of report. This is very efficient tool as it is platform independent. We can generate the report using this tool and can use in any type of applications. Now a days this is very popular in the market.
- SSIS– This tool is used for the integration like duping the data from one database to another like from Oracle to SQL Server or from Excel to SQL Server etc.
This tool is also used for bulk transactions in the database like inserting lac's of records at once. We can create the integration services modules which will do the job foe us. - SSAS– This tool is used to analyse the performance of the SQL server in terms of load balancing, heavy data, transaction etc. So it is more or less related to administration of the SQL Server using this tool. This is very powerful tool and through this we can analyse the data inserting in to the database like how many transactions happens in a second etc.
- SSRS– This tool is related to the generation of report. This is very efficient tool as it is platform independent. We can generate the report using this tool and can use in any type of applications. Now a days this is very popular in the market.
Q. What is SSIS variable?
Variables store values that a SQL Server Integration Services package and its containers, tasks, and event handlers can use at run time.In SSIS two variables are defined
- Global variables:- A global variable is available to all tasks across the entire job.
- Task level variables: - Variables created in tasks are only available within that task.
Q. What is SSIS Control flow?
SSIS control flow allows you to program graphically how the tasks will run by using the logical connectors between tasks.
There are three basic logical connectors that you can use:
The control flow in a SQL Server Integration Services (SSIS) package defines the workflow for that package. Not only does the control flow determine the order in which executable will run, the control flow also determines under what conditions they’re executed.
1. Sequence Container: - Sequence container defines the control flow that is subset of package control flow. Sequence containers group the package into multiple separate control flows, each containing one or more tasks and containers that run within the overall package control flow.
Advantage: -
· Disable the property of sequence container instead of on the individual tasks.
· Change the property of sequence container instead of on the individual tasks.
· Providing scope for variables that a group of related tasks and containers use.
2. For Loop container: - It’s define the repeated control flow in a package. It’s working like loop in programming languages. In each repeated loop for each loop evaluate the expression until the expression is false. Example, you need to update records 5 times, you can place the task that updates the records inside this for loop container and specifies 5 as the end of the loops by using the for loop container.
For loop Properties:-
3. Foreach loop container
It’s defines a repeating control flow in a package. Foreach loop implementation is similar as programming languages. In a package, looping is enabled by using a Foreach enumerator. The Foreach Loop container repeats the control flow for each member of a specified enumerator.
SSIS provides these following enumerator types:
- Success
- Failure
- Complete
Q. What is precedence constraint?
In SSIS, tasks are linked by precedence constraints. A task will only execute if the condition that is set by the precedence constraint preceding the task is met.The control flow in a SQL Server Integration Services (SSIS) package defines the workflow for that package. Not only does the control flow determine the order in which executable will run, the control flow also determines under what conditions they’re executed.
 |
- Success: The precedence executable must run successfully for the constrained executable to run. This is the default value. The precedence constraint is set to green when the Success option is selected.
- Failure: The precedence executable must fail for the constrained executable to run. The precedence constraint is set to red when the Failure option is selected.
- Completion: The constrained executable will run after the precedence executable runs, whether the precedence executable runs successfully or whether it fails. The precedence constraint is set to blue when the Completion option is selected.
Q. Defined the different type of containers in SSIS packages
In SSIS there are three type of Container:-
1. Sequence Container: - Sequence container defines the control flow that is subset of package control flow. Sequence containers group the package into multiple separate control flows, each containing one or more tasks and containers that run within the overall package control flow.
Advantage: -
· Disable the property of sequence container instead of on the individual tasks.
· Change the property of sequence container instead of on the individual tasks.
· Providing scope for variables that a group of related tasks and containers use.
2. For Loop container: - It’s define the repeated control flow in a package. It’s working like loop in programming languages. In each repeated loop for each loop evaluate the expression until the expression is false. Example, you need to update records 5 times, you can place the task that updates the records inside this for loop container and specifies 5 as the end of the loops by using the for loop container.
For loop Properties:-
- InitExpression:- Optionally, provide an expression that initializes values used by the loop.
- EvalExpression:- Provide an expression to evaluate whether the loop should stop or continue.
- AssignExpression:- Optionally, provide an expression that changes a condition each time that the loop repeats.
- Name:- Provide a unique name for the For Loop container. This name is used as the label in the task icon.
- Description:- Provide a description of the For Loop container.
It’s defines a repeating control flow in a package. Foreach loop implementation is similar as programming languages. In a package, looping is enabled by using a Foreach enumerator. The Foreach Loop container repeats the control flow for each member of a specified enumerator.
SSIS provides these following enumerator types:
- ForEach File Enumerator
- Foreach Item Enumerator
- Foreach ADO Enumerator
- Foreach ADO.NET Schema Rowset Enumerator
- Foreach From Variable Enumerator
- Foreach NodeList Enumerator
- Foreach SMO Enumerator
Comments