SSIS Interview Questions and Answers for Experienced and Freshers

SSIS Interview Questions 1

SSIS – Part 1

SSIS Interview Questions and Answers for Experienced and Freshers

 

Here we are publishing series of posts on SSIS Interview questions with answers for experienced and freshers . Below is the series 1.

Q. Define SSIS?

Ans:

SQL Server Integration Services — commonly known as SSIS is the new platform that was introduced in SQL Server 2005, for data transformation and data integration solutions. This replaced the DTS in SQL Server 2000.

Q. Name a few SSIS components?

Ans:

  • Integration Services Projects
  • Integration Services Packages
  • Control Flow Elements
  • Data Flow Elements
  • Integration Services Connections
  • Integration Services Variables
  • Integration Services Event Handlers
  • Integration Services Log Providers

Q. What is a project and Package in SSIS?

Ans:

Project is a container for developing packages. Package is nothing but an object. It implements the functionality of ETL — Extract, Transform and Load — data.

Q. What are the 4 elements (tabs) that you see on a default package designer in BIDS?

Ans:

Control Flow, Data Flow, event Handler and package explorer. (Parameters – 2012 Data Tools)

Q. What is a Control flow and Data Flow elements in SSIS?

Ans:

Control Flow:

Control flow element is one that performs any function or provides structure or control the flow of the elements. There must be at least one control flow element in the SSIS package. In SSIS a workflow is called a control-flow. A control-flow links together our modular data-flows as a series of operations in order to achieve a desired result.

A control flow consists of one or more tasks and containers that execute when the package runs. To control order or define the conditions for running the next task or container in the package control flow

Data Flow:

All ETL tasks related to data are done by data flow elements. It is not necessary to have a data flow element in the SSIS package. A data flow consists of the sources and destinations that extract and load data, the transformations that modify and extend data, and the paths that link sources, transformations, and destinations. Before you can add a data flow to a package, the package control flow must include a Data Flow task. The Data Flow task is the executable within the SSIS package that creates, orders, and runs the data flow. A separate instance of the data flow engine is opened for each Data Flow task in a package.

Q. What are the 3 different types of control flow elements in SSIS?

Ans:

  • Structures provided by Containers
  • Functionality provided by Tasks
  • Precedence constraints that connect the executables, containers, and tasks into an ordered control flow.

Q. What are the 3 data flow components in SSIS?

Ans:

  • Source
  • Transformation
  • Destination

Q. What are connections and connection managers in SSIS?

Ans:

Connection as its name suggests is a component to connect to any source or destination from SSIS — like a sql server or flat file or lot of other options that SSIS provides. Connection manager is a logical representation of a connection.

Q. What is the use of Check Points in SSIS?

Ans:

SSIS provides a Checkpoint capability which allows a package to restart at the point of failure.

Q. What are the command line tools to execute SQL Server Integration Services packages?

Ans:

DTSEXECUI – When this command line tool is run a user interface is loaded in order to configure each of the applicable parameters to execute an SSIS package.

DTEXEC – This is a pure command line tool where all of the needed switches must be passed into the command for successful execution of the SSIS package.

Q. Can you explain the SQL Server Integration Services functionality in Management Studio?

Ans:

You have the ability to do the following:

  • Login to the SQL Server Integration Services instance
  • View the SSIS log
  • View the packages that are currently running on that instance
  • Browse the packages stored in MSDB or the file system
  • Import or export packages
  • Delete packages
  • Run packages

Q. Can you name some of the core SSIS components in the Business Intelligence Development Studio you work with on a regular basis when building an SSIS package?

Ans:

  • Connection Managers
  • Control Flow
  • Data Flow
  • Event Handlers
  • Variables window
  • Toolbox window
  • Output window
  • Logging
  • Package Configurations

Q. Name Transformations available in SSIS?

Ans:

DATACONVERSION: Converts columns data types from one to another type. It stands for Explicit Column Conversion.

DATAMININGQUERY: Used to perform data mining query against analysis services and manage Predictions Graphs and Controls.

DERIVEDCOLUMN: Create a new (computed) column from given expressions.

EXPORTCOLUMN: Used to export a Image specific column from the database to a flat file.

FUZZYGROUPING: Used for data cleansing by finding rows that are likely duplicates.

FUZZYLOOKUP: Used for Pattern Matching and Ranking based on fuzzy logic.

AGGREGATE: It applies aggregate functions to Record Sets to produce new output records from aggregated values.

AUDIT: Adds Package and Task level Metadata: such as Machine Name, Execution Instance, Package Name, Package ID, etc..

CHARACTERMAP: Performs SQL Server column level string operations such as changing data from lower case to upper case.

MULTICAST: Sends a copy of supplied Data Source onto multiple Destinations.

CONDITIONALSPLIT: Separates available input into separate output pipelines based on Boolean Expressions configured for each output.

COPYCOLUMN: Add a copy of column to the output we can later transform the copy keeping the original for auditing.

IMPORTCOLUMN: Reads image specific column from database onto a flat file.

LOOKUP: Performs the lookup (searching) of a given reference object set to a data source. It is used for exact matches only.

MERGE: Merges two sorted data sets into a single data set into a single data flow.

MERGEJOIN: Merges two data sets into a single dataset using a join junction.

ROWCOUNT: Stores the resulting row count from the data flow / transformation into a variable.

ROWSAMPLING: Captures sample data by using a row count of the total rows in dataflow specified by rows or percentage.

UNIONALL: Merge multiple data sets into a single dataset.

PIVOT: Used for Normalization of data sources to reduce anomalies by converting rows into columns

UNPIVOT: Used for de-normalizing the data structure by converts columns into rows in case of building Data Warehouses.

References:

For more MSBI stuff please have a look at below references:

http://blogs.msdn.com/b/business-intelligence

https://sreenivasmsbi.wordpress.com

http://www.msbiguide.com

http://msbiravindranathreddy.blogspot.in

http://sqlschool.com/MSBI-Interview-Questions.html

https://www.katieandemil.com

http://www.venkateswarlu.co.in

http://www.sqlserverquest.com

http://www.msbiguide.com

Posted in Interview Q&A, MSBI, SSIS | Tagged , , , , , , , , , , , , , , , | 39 Comments

Leave a Reply

39 Comments on "SSIS Interview Questions and Answers for Experienced and Freshers"

Notify of
avatar
Sort by:   newest | oldest | most voted
venkatesh
Guest

HI,
Mr.Udayarumilli,Thanks for the KT.Please continue this..

Narendra
Guest

Hi,

Thanks for uploading SSIS interview Questions. I Learn many topics from this website.

Indu
Guest

Amazing stuff !! Really appreciate your efforts ..

teja
Guest

Hi Indu

i am looking for help with interview, would you be able to help

kalpana
Guest

present iam working on 3d modelling but i want to put the effort on msbi
can u plz tell me the life of msbi
it is having future or not

teja
Guest

hi kalpana, i am looking for help with interview, would you be able to help me with it

kalpana
Guest

can u plz tell me best site for msbi

rajtreo
Guest

Do you have installation video guide for MSBI?

aamala
Guest

Hai Rajtreo,

Visit that link https://www.youtube.com/watch?v=wHR-Q-sxVPk

teja
Guest

hello aamala

i am looking for interview help, would you be interested in helping

Lokesh
Guest

Thanks for giving big information in msbi
Thanks allot. Good job

chinmayesri
Guest

nice post thank you…

Prabhakar Pandey
Guest

Dear Sir,
I am very much passionate about SQl and MSBI and as when I get the chance for learn something new then I go through your blog and learned lots of concept MSBI concern.
But I want to design a Data warehouse that’s a OLAP system from OLTP, getting confusion that how to start designing the DW system.Please guide me….or I need a DW project that why I can learn.
Your response will be highly appreciable for me…

Thanks & Regards
Prabhakar Pandey
Email- prabhakarpandey.100@gmail.com

Sesank
Guest

Hi….
I’m attending interviews these days, so interviewer asked to me that is
what do you develop in ssis package?
how to say answer for this question, if you know Pls guide me sir.

kris maly
Guest

Awesome

Please keep writing some more based on your practical experience.

I enjoyed reading this article and useful/helpful.

Thanks for educating community.

satish kumar
Guest

Thanks for giving big information in msbi

mythily mythu
Guest

Thanks for giving big information…

Sesank
Guest

Hi to all….
I’m attending interviews these days, so interviewer asked to me that is
what do you develop in ssis package?
how to say answer for this question, Pls guide me any one….

Pavan
Guest

Hi Udayarumilli,
Thanks for your stuff..can you please share real time interview questions and real time experiences??

satish reddy
Guest

Hi Admin,

You have an excellent collection of SSIS interview question,

Recently we have updated all the latest SSIS interview on our post, if you like our team efforts, please add our blog post to further reference which will help your loyal visitors.

Top 100 ssis interview questions
SSIS Interview Questions for freshers and experienced

Regards,
Satish

Srihari
Guest

Hi Uday,
Really helpful. Thanks for your time to sharing the valuable information

wpDiscuz