SSIS – 3 UPDATED ACTUAL Exam
Questions and CORRECT Answers
- CORRECT ANSWER -
Difference between Control Flow vs Data Flow Tasks - CORRECT ANSWER - Control
Flow:
Control Flow is part of SQL Server Integration Services Package where you handle the flow of
operations or Tasks. Let's say you are reading a text file by using Data Flow task from a folder. If
Data Flow Task completes successfully then you want to Run File System Task to move the file
from Source Folder to Archive Folder. If Data Flow Task failed then you want to send email to
your users by using Send Mail Task. The Precedence Constraints are used to control the
execution flow.
Data Flow:
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.).
After extacting data Data Flow Transformations such as Data Conversion,
Derived Column,
Lookup,
Multicast,
Merge
etc are used to implement different business logics and finally written to Data Flow Destinations
(OLE DB Destination,
Flat File Destination,
Excel Destination,
DataReader Destination ADO NET Destination etc.)
,What are different types of containers that can be used with SSIS packages. - CORRECT
ANSWER - There are three types of containers:
sequence,
for loops and,
for each loops.
A sequence container is a simple way to group similar tasks together.
Think of a sequence container as an organization container for more complex SSIS packages.
A for loop contain is what you can use to repeat steps a certain number of times. Perhaps you
need to update records ten times. You can use this for loop container to complete a number of
tasks 10 times before moving on to another step.
A for each loop can only be used in a collection. A collection is an enumerated (перечисленных,
номерованных) object. For instance, you can use the for each loop to a specific number of
records in a record set or a certain number of files in a directory. The for each loop is better than
the standard for loop, because you don't accidentally make a mistake of looping through
numerical values too many times and causing an error in your SSIS package.
List various types of files or connections that support SSIS. - CORRECT ANSWER -
Excel
NetSQLClient
Flat File
XML - eXtensible Markup Language
ODBC - Open Database Connectivity
OLEDB - Object Linking and Embedding, Database
1. What is the Precedence Constraint in SSIS. Types? - CORRECT ANSWER - The
logical task sequence can be defined by precedence constraint, logical sequence is basically the
order of the tasks in which they should be executed. All tasks can be connected by using
precedence constraints.
1. Differentiate between Union All and Merge Transformations. - CORRECT ANSWER -
Data from two paths can be merged into a single path by Merge transformation. This transform is
, found useful when the data flows through a path that can handle certain errors and merge back it
into the main data source. Prior to merging transformation data should be sorted that can be done
by using sorting transformation. For all paths metadata must be the same as the customer id field
type cannot be numeric type is one path and character type in another. Union All transformation
works like the Merge transformation, but here in this transformation sorted data is not required.
Here the output from multiple sources is taken and then it is transformed and combined to form a
single result set.
What is Multicast transformation? Ole DB Command Transformation? Row Count? Slowly
Changing Dimension? - CORRECT ANSWER - The Multicast transformation distributes
its input to one or more outputs. This transformation is similar to the Conditional Split
transformation. Both transformations direct an input to multiple outputs. The difference between
the two is that the Multicast transformation directs every row to every output, and the
Conditional Split directs a row to a single output.
Using the Multicast transformation, a package can create logical copies of data. This capability is
useful when the package needs to apply multiple sets of transformations to the same data.
The OLE DB Command transformation runs an SQL statement for each row in a data flow. For
example, you can run an SQL statement that inserts, updates, or deletes rows in a database table.
You can configure the OLE DB Command Transformation in the following ways:
· Provide the SQL statement that the transformation runs for each row.
· Specify the number of seconds before the SQL statement times out.
· Specify the default code page.
Typically, the SQL statement includes parameters. The parameter values are stored in external
columns in the transformation input, and mapping an input column to an external column maps
an input column to a parameter.
The Row Count transformation counts rows as they pass through a data flow and stores the final
count in a variable.
A SQL Server Integration Services package can use row counts to update the variables used in
scripts, expressions, and property expressions. (For example, the variable that stores the row
count can update the message text in an e-mail message to include the number of rows.) The
variable that the Row Count transformation uses must already exist, and it must be in the scope
of the Data Flow task to which the data flow with the Row Count transformation belongs.
The transformation stores the row count value in the variabl
Questions and CORRECT Answers
- CORRECT ANSWER -
Difference between Control Flow vs Data Flow Tasks - CORRECT ANSWER - Control
Flow:
Control Flow is part of SQL Server Integration Services Package where you handle the flow of
operations or Tasks. Let's say you are reading a text file by using Data Flow task from a folder. If
Data Flow Task completes successfully then you want to Run File System Task to move the file
from Source Folder to Archive Folder. If Data Flow Task failed then you want to send email to
your users by using Send Mail Task. The Precedence Constraints are used to control the
execution flow.
Data Flow:
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.).
After extacting data Data Flow Transformations such as Data Conversion,
Derived Column,
Lookup,
Multicast,
Merge
etc are used to implement different business logics and finally written to Data Flow Destinations
(OLE DB Destination,
Flat File Destination,
Excel Destination,
DataReader Destination ADO NET Destination etc.)
,What are different types of containers that can be used with SSIS packages. - CORRECT
ANSWER - There are three types of containers:
sequence,
for loops and,
for each loops.
A sequence container is a simple way to group similar tasks together.
Think of a sequence container as an organization container for more complex SSIS packages.
A for loop contain is what you can use to repeat steps a certain number of times. Perhaps you
need to update records ten times. You can use this for loop container to complete a number of
tasks 10 times before moving on to another step.
A for each loop can only be used in a collection. A collection is an enumerated (перечисленных,
номерованных) object. For instance, you can use the for each loop to a specific number of
records in a record set or a certain number of files in a directory. The for each loop is better than
the standard for loop, because you don't accidentally make a mistake of looping through
numerical values too many times and causing an error in your SSIS package.
List various types of files or connections that support SSIS. - CORRECT ANSWER -
Excel
NetSQLClient
Flat File
XML - eXtensible Markup Language
ODBC - Open Database Connectivity
OLEDB - Object Linking and Embedding, Database
1. What is the Precedence Constraint in SSIS. Types? - CORRECT ANSWER - The
logical task sequence can be defined by precedence constraint, logical sequence is basically the
order of the tasks in which they should be executed. All tasks can be connected by using
precedence constraints.
1. Differentiate between Union All and Merge Transformations. - CORRECT ANSWER -
Data from two paths can be merged into a single path by Merge transformation. This transform is
, found useful when the data flows through a path that can handle certain errors and merge back it
into the main data source. Prior to merging transformation data should be sorted that can be done
by using sorting transformation. For all paths metadata must be the same as the customer id field
type cannot be numeric type is one path and character type in another. Union All transformation
works like the Merge transformation, but here in this transformation sorted data is not required.
Here the output from multiple sources is taken and then it is transformed and combined to form a
single result set.
What is Multicast transformation? Ole DB Command Transformation? Row Count? Slowly
Changing Dimension? - CORRECT ANSWER - The Multicast transformation distributes
its input to one or more outputs. This transformation is similar to the Conditional Split
transformation. Both transformations direct an input to multiple outputs. The difference between
the two is that the Multicast transformation directs every row to every output, and the
Conditional Split directs a row to a single output.
Using the Multicast transformation, a package can create logical copies of data. This capability is
useful when the package needs to apply multiple sets of transformations to the same data.
The OLE DB Command transformation runs an SQL statement for each row in a data flow. For
example, you can run an SQL statement that inserts, updates, or deletes rows in a database table.
You can configure the OLE DB Command Transformation in the following ways:
· Provide the SQL statement that the transformation runs for each row.
· Specify the number of seconds before the SQL statement times out.
· Specify the default code page.
Typically, the SQL statement includes parameters. The parameter values are stored in external
columns in the transformation input, and mapping an input column to an external column maps
an input column to a parameter.
The Row Count transformation counts rows as they pass through a data flow and stores the final
count in a variable.
A SQL Server Integration Services package can use row counts to update the variables used in
scripts, expressions, and property expressions. (For example, the variable that stores the row
count can update the message text in an e-mail message to include the number of rows.) The
variable that the Row Count transformation uses must already exist, and it must be in the scope
of the Data Flow task to which the data flow with the Row Count transformation belongs.
The transformation stores the row count value in the variabl
