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 too...

  1. 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


  1. 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.
  2. 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.
  3. 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:
  • 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. 

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:



  • 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

Post a comment

emo-but-icon

Follow Us

Recent

Comments

Donate

Side Ads

Text Widget

Connect Us

Sql Server 2017 Interview Questions
Sql Server Basic Interview Query Set-1
Sql Server Basic Interview Query Set-2
Sql Server Date-Time Interview Query SET-3
Sql Server Salary Interview Query SET-4
Sql Server Group By Interview Query SET-5
Sql Server Join Interview Query SET-6
Sql Server Tricky Join Interview Query SET-7
Sql Server DDL Interview Query SET-8
Small but very Tricky Sql Server Query SET-9
Very Much Tricky Query (not 4 fresher)SET-10
Sql Server Complex Interview Query SET-11
Sql Server Datatype Interview Questions
Sql Server View Interview Questions
Sql Server Index Interview Questions
TCS Tricky Sql Interview Queries
HCL Sql Interview Queries
Sql Server SP Interview Questions
Sql Server Trigger Interview Questions
Sql Server Temp Table Interview Questions
Sql Server 2016 Interview Questions
Sql Server Performance Tuning Interview Q.
Sql Server Constraints Interview Questions
Sql Server Storage/Size Interview Questions
Sql Server Very Basic Interview Questions
Sql Server Quiz for Fresher
Sql Server Icon Quiz for All
Sql Server 300+ Theoretical Interview Questions
Sql Server Complete Set

ASP.NET AND JQUERY INTERVIEW QUESTION
ASP.NET: Web Config Interview Question
ASP.NET: View State Interview Question
ASP.NET: Session Interview Question
ASP.NET: Session Interview Question 
ASP.NET: Security Interview Question
ASP.NET: Catching Interview Question
C# OOPS Interview Question
MORE Interview Question
JQUERY: Interview Question Set-1
JQUERY: Interview Question Set-2
JAVA-SCRIPT: Interview Question Set-1
JAVA-SCRIPT: Interview Question Set-2
ASP.NET MVC: Interview Question Set-1

POPULAR POSTS
10 MOST DIFFICULT INTERVIEW QUESTIONS
5 BEST INTERVIEW TIPS
YOUR 10 MISTAKE DURING INTERVIEW
2016 TOP 20 GROUP DISCUSSION TOPICS
HOW SQL PERFORMANCE AFFECTED BY DATATYPE
item