Exam (elaborations)
SSIS INTERVIEW QUESTIONS AND 100% VERIFIED ANSWERS
- Course
- Institution
SSIS INTERVIEW QUESTIONS AND 100% VERIFIED ANSWERS...
[Show more]
Content preview
SSIS INTERVIEW QUESTIONS AND 100%VERIFIED ANSWERS
What is ETL? - ANSWER Extract, Transform, Load
What is Business Intelligence? - ANSWER Business intelligence (BI) is
a technology-driven process for analyzing data and presenting
actionable information to help executives, managers and other corporate
end users make informed business decisions.
Which version of SSIS have you worked with? - ANSWER SQL Version
Build # PackageFormatVersion Visual Studio Version
2005 9 2 2005
2008 10 3 2008
2008 R2 10.5 3 2008
2012 11 6 2010 or BI 2012
2014 12 8 2012 CTP2 or 2013
2016 13 8 2015/2017
2017 14 8 2017
What additional features are available in SSIS 2012 that were not in
SSIS 2008? - ANSWER The major difference that has come up in SSIS
2012 is a new deployment model called project deployment model. As
per this package is no longer a basic unit but its the project. When you
use this deployment model you can deploy project from Visual
Studio/SSDT environment directly to the server.
What is the difference between Control Flow and Data Flow in SSIS? -
ANSWER Control Flow is part of SQL Server Integration Services
Package where you handle the flow of operations or Tasks.
and
,Data Flow is the part of SQL Server Integration Services Package,
where data is extracted by using Data Flow Sources ( OLE DB Source,
Raw File Source, Flat File Source , Excel Source etc.).
What is parallel execution in SSIS, and how many Data Flow Tasks can
a package run in parallel? - ANSWER In simple words, If you place
more than one Task on Control Flow pane and do not connect them by
using Precedence constraint, the Tasks will run in Parallel.
What is the MaxConcurrentExecutables property on a Package level? -
ANSWER The MaxConcurrentExecutables property is a property of the
package. This property defines how many tasks can run simultaneously;
by specifying the maximum number of SSIS threads that can execute in
parallel per package. The default value is -1, which equates to the
number of physical or logical processors plus 2.
What is the Engine Thread property of Data Flow Task? - ANSWER
The EngineThreads property is a property of each Data Flow task. This
property defines how many threads the data flow engine can create and
run in parallel. The EngineThreads property applies equally to both the
source threads that the data flow engine creates for sources and the
worker threads that the engine creates for transformations and
destinations.
What are the Precedence Constraints in SSIS, and where and why have
you used them? - ANSWER Precedence constraints help us create a
work flow in a SSIS package. With the help of precedence constraints
you can connect tasks to control the flow. Based on the configuration,
the precedence constraints can be represented as solid or dotted lines
with green, red or blue color.We need a precedence executable and a
constrained executable to successfully create a precedence constraint.
What is the difference between the Success and the Completion value of
Precedence Constraint? - ANSWER A precedence link between two
tasks establishes a relationship between the tasks. The second task (the
dependent task) executes only if the execution result of the first task (the
precedent task) matches specified criteria. Typically the execution result
specified is Success, Failure, or Completion
, What is the DelayValidation property of Data Flow Task? Why does one
use this property? - ANSWER Delay Validation Property indicates
whether validation of the task is delayed until run time. The default value
is false. False if the package is validated, and errors and warnings are
returned before the package is actually executed. we always use this
property when using temp table, dynamic values in the task. Or at the
time when some property values would be changed at runtime.
What is RetainSameConnection Property on Connection Manager in
SSIS Package? Why is it used? - ANSWER Whenever a task uses a
connection manager to connect to source or destination database, a
connection is opened and closed with the execution of that task.
Sometimes you might need to open a connection, execute multiple tasks
and close it at the end of the execution. This is where
RetainSameConnection property of the connection manager might help
you. When you set this property to TRUE, the connection will be opened
on first time it is used and remain open until execution of the package
completes.There are two use cases for the RetainSameConnection:
Native SQL transaction across multiple tasks
Using temporary table in multiple tasks
If we create a temp table in SSIS Package and want to use it in other
tasks, which properties do we need to use? - ANSWER Step #1.
[Execute SQL Task] - it used to create the temp table ahead
(Connection -> Source)
Step #2. [Data Flow Task] - [OLE DB Source] prepare one variable to
store the source query which need to join with temp table to do the filter .
e.x: select .. from tableA join #tempB on ... . One more thing need to
highlight: To make sure the [OLE DB Source] can get the metadata for
input column and output column , it need to create one real table which
has the same schema with the temp table .
What is data Viewer in SSIS? Is data viewer available in ControlFlow or
Data Flow? - ANSWER Data viewers are a very important feature in
SSIS for debugging your data pump pipeline. They allow you to view
The benefits of buying summaries with Stuvia:
Guaranteed quality through customer reviews
Stuvia customers have reviewed more than 700,000 summaries. This how you know that you are buying the best documents.
Quick and easy check-out
You can quickly pay through credit card or Stuvia-credit for the summaries. There is no membership needed.
Focus on what matters
Your fellow students write the study notes themselves, which is why the documents are always reliable and up-to-date. This ensures you quickly get to the core!
Frequently asked questions
What do I get when I buy this document?
You get a PDF, available immediately after your purchase. The purchased document is accessible anytime, anywhere and indefinitely through your profile.
Satisfaction guarantee: how does it work?
Our satisfaction guarantee ensures that you always find a study document that suits you well. You fill out a form, and our customer service team takes care of the rest.
Who am I buying these notes from?
Stuvia is a marketplace, so you are not buying this document from us, but from seller luzlinkuz. Stuvia facilitates payment to the seller.
Will I be stuck with a subscription?
No, you only buy these notes for $15.49. You're not tied to anything after your purchase.
Can Stuvia be trusted?
4.6 stars on Google & Trustpilot (+1000 reviews)
78252 documents were sold in the last 30 days
Founded in 2010, the go-to place to buy study notes for 14 years now