Want To Improve Your Business Intelligence? Take This MSBI Quiz

Approved & Edited by ProProfs Editorial Team
At ProProfs Quizzes, our dedicated in-house team of experts takes pride in their work. With a sharp eye for detail, they meticulously review each quiz. This ensures that every quiz, taken by over 100 million users, meets our standards of accuracy, clarity, and engagement.
Learn about Our Editorial Process
| Written by LmAkshay
L
LmAkshay
Community Contributor
Total Contribution - 2 | Total attempts - 8,192
Questions: 61 | Attempts: 5,541

SettingsSettingsSettings
Want To Improve Your Business Intelligence? Take This MSBI Quiz - Quiz

MSBI stands for Microsoft Business Intelligence. This powerful suite is composed of tools which helps in providing best solutions for Business Intelligence and Data Mining Queries. This tool uses Visual studio along with SQL server.
Want To Improve Your Business Intelligence? Take This MSBI Quiz


Questions and Answers
  • 1. 

    You are in the process of creating a data flow to load sales data into a fact table. You have configured a full cache mode Lookup Transformation in the data flow to find the product data for sales. Two tables host the lookup source for the product data.You then access the Advance Page of the Lookup Transformation Editor.Which of the following is a reason for doing this? 

    • A. 

      To configure a suitable connection manager.

    • B. 

      To configure partial caching.

    • C. 

      To modify the SQL statement for the Lookup transformation.

    • D. 

      To specify the join between the source table and the reference table.

    Correct Answer
    A. To configure a suitable connection manager.
  • 2. 

    You have recently created an SQL Server Integration Services (SSIS) project catalog that contains two Environments. A single Environment Variable, named ConnectionString, of type string is specified by each environment. The project also has a project Connection Manager configured to link up with the data warehouse.You have been instructed to make sure that deployed packages are executed using any of the specified Environments.Which of the following suitably describes an Environment Variable?

    • A. 

      An environment variable specifies the data that will be used by a package.

    • B. 

      An environment variable can be used to manage Integration Services objects in the catalog.

    • C. 

      An environment variable defines a literal value that can be assigned to a parameter during package execution.

    • D. 

      An environment variable allows you to use folders to organize your projects and environments

    Correct Answer
    C. An environment variable defines a literal value that can be assigned to a parameter during package execution.
    Explanation
    An environment variable defines a literal value that can be assigned to a parameter during package execution. This means that the variable can hold a specific value that can be used within the package at runtime. It allows for flexibility and customization, as different values can be assigned to the variable depending on the specific execution scenario. This helps in managing and configuring the package to use different values based on the environment it is being executed in.

    Rate this question:

  • 3. 

    Which container is not visible from the IDE? 

    • A. 

      Sequence Container

    • B. 

      For Loop Container

    • C. 

      For Each Loop Container

    • D. 

      Task Host Container

    Correct Answer
    D. Task Host Container
    Explanation
    The Task Host Container is not visible from the IDE. The Task Host Container is responsible for hosting and executing tasks within the SSIS package, but it is not directly visible or accessible from the IDE. This container is used to manage the execution of tasks and does not contain any tasks or control flow elements itself.

    Rate this question:

  • 4. 

    You work as a senior database administrator at ABC.com. The ABC.com network consists of a single domain named ABC.com. ABC.com makes use of Microsoft SQL Server 2012 in their environment. You are running a training exercise for Microsoft SQL Server 2012 junior administrators. You are discussing the use of Slowly Changing Dimension Transformation Outputs. One of the output options causes Derived Column transformations to create columns for the expired row and the current row indicators. Which option is the output that causes this?

    • A. 

      Inferred Member Updates Output

    • B. 

      Historical Attributes Inserts Output

    • C. 

      Fixed Attribute Output

    • D. 

      Changing Attributes Updates Output

    Correct Answer
    B. Historical Attributes Inserts Output
    Explanation
    The output option that causes Derived Column transformations to create columns for the expired row and the current row indicators is the Historical Attributes Inserts Output. This option is used when there are changes in historical attributes and it inserts new rows with the updated attributes, while also keeping the expired rows with indicators to distinguish them from the current rows.

    Rate this question:

  • 5. 

    You work as a senior database developer at ABC.com. The ABC.com network consists of a single domain named ABC.com. ABC.com makes use of Microsoft SQL Server 2012 in their environment. You are running a training exercise for Microsoft SQL Server 2012 junior developers. You are discussing the ValidateExternalMetaData property for a data flow. Which of the following represents the data type for this property? 

    • A. 

      Integer

    • B. 

      IDTSExternalMetadataColumn100

    • C. 

      String

    • D. 

      Boolean

    Correct Answer
    D. Boolean
    Explanation
    The ValidateExternalMetaData property for a data flow in Microsoft SQL Server 2012 is of the data type Boolean. This means that the property can have a value of either true or false, indicating whether or not the external metadata should be validated.

    Rate this question:

  • 6. 

    You work as a senior database administrator at ABC.com. The ABC.com network consists of a single domain named ABC.com. ABC.com makes use of Microsoft SQL Server 2012 in their environment. You are hosting a training exercise for Microsoft SQL Server 2012 administrators. You are currently discussing Multicast transformation. Which of the following is TRUE with regards to Multicast transformation?

    • A. 

      It prevents a package from creating logical copies of data.

    • B. 

      It allows a package to create logical copies of data.

    • C. 

      It distributes its input to one or more outputs.

    • D. 

      It distributes its input to a single output.

    Correct Answer(s)
    B. It allows a package to create logical copies of data.
    C. It distributes its input to one or more outputs.
    Explanation
    The Multicast transformation in Microsoft SQL Server 2012 allows a package to create logical copies of data and also distributes its input to one or more outputs. This means that the Multicast transformation can be used to split the data flow into multiple paths, allowing for parallel processing or routing the data to different destinations.

    Rate this question:

  • 7. 

    You are creating a SQL Server 2008 Integration Services (SSIS) package on a SQL Server 2008 database for Company.com. In order to develop a failure recovery plan that is published for a SQL Server. Which is the correct answer?

    • A. 

      You should back up the master database.

    • B. 

      You should back up the local database.

    • C. 

      You should back up the system database.

    • D. 

      You should back up the systemdb databse.

    Correct Answer
    A. You should back up the master database.
    Explanation
    The master database in SQL Server 2008 contains the system configuration information and metadata for all other databases on the server. It is crucial to back up the master database in order to ensure that the server can be recovered in case of a failure. Backing up the local database, system database, or systemdb database alone would not be sufficient for a complete failure recovery plan.

    Rate this question:

  • 8. 

    In the event of an Execute SQL task failing, changes made by all of the Execute SQL tasks is rolled back.To do that which of the following is the value that must be set for the TransactionOption property of the package?

    • A. 

      NotSupported

    • B. 

      Supported

    • C. 

      Required

    • D. 

      NotRequired

    Correct Answer
    C. Required
    Explanation
    If the TransactionOption property of the package is set to "Required", it means that the package requires a transaction to be started. In the event of an Execute SQL task failing, the changes made by all of the Execute SQL tasks will be rolled back, ensuring that the database remains in a consistent state.

    Rate this question:

  • 9. 

    What are the main component of SSIS (project-architecture)?

    • A. 

      SSIS service

    • B. 

      SSIS runtime engine & runtime executables

    • C. 

      SSIS dataflow engine & dataflow components

    • D. 

      SSIS clients

    • E. 

      All the above

    Correct Answer(s)
    A. SSIS service
    B. SSIS runtime engine & runtime executables
    C. SSIS dataflow engine & dataflow components
    Explanation
    The correct answer is "All the above." The main components of SSIS project architecture include the SSIS service, SSIS runtime engine and runtime executables, SSIS dataflow engine and dataflow components, and SSIS clients.

    Rate this question:

  • 10. 

    What are the effects of setting IsolationLevel transaction property of a Data Flow task to Chaos?

    • A. 

      It prevents pending changes from more highly isolated transactions from being overwritten

    • B. 

      It prevents shared locks from being issued and exclusive locks from being honored.

    • C. 

      It prevents other users from updating the data.

    • D. 

      It reduces blocking by storing a version of data that one application can read while another is modifying the same data.

    Correct Answer
    A. It prevents pending changes from more highly isolated transactions from being overwritten
    Explanation
    Setting the IsolationLevel transaction property of a Data Flow task to Chaos prevents pending changes from more highly isolated transactions from being overwritten. This means that if there are multiple transactions occurring simultaneously, the changes made by a transaction with a higher isolation level will not be overwritten by a transaction with a lower isolation level. This helps maintain data integrity and ensures that changes made by more isolated transactions are not lost or overridden.

    Rate this question:

  • 11. 

    How many Data Flow Tasks can a package run in parallel?

    • A. 

      Total Tasks=Number of processors of machine+2

    • B. 

      Total task=100

    • C. 

      Total task=10

    • D. 

      Total task=6

    Correct Answer
    A. Total Tasks=Number of processors of machine+2
    Explanation
    The correct answer is "Total Tasks=Number of processors of machine+2". This is because the number of Data Flow Tasks that can run in parallel is determined by the number of processors available on the machine plus 2. This formula allows for optimal utilization of the available resources and helps to maximize the efficiency of the package execution.

    Rate this question:

  • 12. 

    Which of the following is TRUE with regards to the Fuzzy Lookup similarity threshold setting?

    • A. 

      Increasing the threshold can improve the speed of matching.

    • B. 

      Increasing the threshold can deteriorate the speed of matching

    • C. 

      The nearer the value is to 1, the nearer the resemblance of the lookup value to the source value must be to qualify as a match

    • D. 

      The nearer the value is to 1, the further the resemblance of the lookup value to the source value must be to qualify as a match.

    Correct Answer(s)
    A. Increasing the threshold can improve the speed of matching.
    C. The nearer the value is to 1, the nearer the resemblance of the lookup value to the source value must be to qualify as a match
    Explanation
    Increasing the threshold can improve the speed of matching because a higher threshold allows for a broader range of similarity between the lookup value and the source value to qualify as a match. This means that the algorithm does not have to spend as much time and resources comparing and evaluating potential matches. Additionally, the statement that "the nearer the value is to 1, the nearer the resemblance of the lookup value to the source value must be to qualify as a match" further supports the idea that increasing the threshold allows for a looser definition of similarity, which can lead to faster matching.

    Rate this question:

  • 13. 

    If you need to create a sequence number for input records, which transformation will you use?

    • A. 

      Script component

    • B. 

      Derived column

    • C. 

      Row Count

    • D. 

      None

    Correct Answer
    A. Script component
    Explanation
    The Script component is the correct answer because it allows you to write custom code in various programming languages, such as C# or VB.NET, to perform complex transformations on input records. With the Script component, you can create a sequence number for each input record by implementing logic to increment a counter variable for each record processed. This provides the flexibility to generate a unique sequence number based on specific requirements or business rules. The Derived column transformation is used to create new columns based on expressions or calculations, but it does not provide the capability to generate a sequence number. The Row Count transformation simply counts the number of rows passing through it and does not generate a sequence number.

    Rate this question:

  • 14. 

    Which tasks run data flows to extract data, apply column level transformations, and load data? 

    • A. 

      Data Flow Tasks

    • B. 

      Data Preparation Tasks

    • C. 

      Workflow Tasks

    • D. 

      SQL Server tasks

    Correct Answer
    A. Data Flow Tasks
    Explanation
    Data Flow Tasks are responsible for running data flows to extract data, apply column level transformations, and load data. These tasks allow users to define the flow of data from various sources, apply transformations to the data, and then load it into the desired destination. This can include tasks such as extracting data from databases or files, applying transformations like filtering or aggregating, and finally loading the transformed data into a target database or file.

    Rate this question:

  • 15. 

    Which options can be used to group/ungroup components?

    • A. 

      Control Flow Tasks

    • B. 

      Data Flow Tasks

    • C. 

      Event Handlers

    • D. 

      Maintenence Tasks

    Correct Answer
    A. Control Flow Tasks
    Explanation
    Control Flow Tasks can be used to group/ungroup components in a system. Control Flow Tasks are used to define the workflow and sequence of tasks in an SSIS package. They allow for the logical grouping and ordering of components, making it easier to manage and maintain the package. By using Control Flow Tasks, components can be grouped together and executed in a specific order, ensuring that the package runs smoothly and efficiently.

    Rate this question:

  • 16. 

    You are in the process of creating a SQL Server Integration Services (SSIS) project. You have created an initial package that is configured to include a connection manager that was created at the package level. The connection manager is used to access a flat file.Subsequent to creating more packages within the project, you change the package ConnectionManager in the initial package to a project Connection Manager.Which of the following is a reason for doing so?

    • A. 

      It allows all packages in the project to access the flat file.

    • B. 

      It only allows the initial package in the project to access the flat file.

    • C. 

      It only allows the additional packages in the project to access the flat file.

    • D. 

      It prevents all packages in the project from accessing the flat file.

    Correct Answer
    A. It allows all packages in the project to access the flat file.
    Explanation
    By changing the package ConnectionManager to a project Connection Manager, all packages in the project are able to access the flat file. This means that any new packages created within the project will be able to use the same connection manager to access the flat file without the need to create a new one. This promotes reusability and consistency across the project, as all packages can now share the same connection manager and access the flat file in a standardized manner.

    Rate this question:

  • 17. 

    You are creating a SQL Server Integration Services (SSIS) package that implements a Type 3 Slowly Changing Dimension (SCD). You need to add a task or component to the package that allows you to implement the SCD logic. What should you use? 

    • A. 

      A Script component

    • B. 

      An SCD component

    • C. 

      An Aggregate component

    • D. 

      A Merge component

    Correct Answer
    D. A Merge component
    Explanation
    The Merge component in SQL Server Integration Services (SSIS) is used to implement the Type 3 Slowly Changing Dimension (SCD) logic. The Merge component allows you to compare and merge data from multiple sources based on matching key columns. In the context of an SCD, it can be used to compare the incoming data with the existing data in the dimension table and determine whether to insert a new record, update an existing record, or do nothing. Therefore, using a Merge component is the correct choice for implementing SCD logic in an SSIS package.

    Rate this question:

  • 18. 

    Which of the following is not a feature of SQL Server 2008 R2 Enterprise? 

    • A. 

      Hot-add CPU

    • B. 

      Hot-add RAM

    • C. 

      Database partitioning

    • D. 

      In memory database

    Correct Answer
    D. In memory database
    Explanation
    SQL Server 2008 R2 Enterprise does not have the feature of an in-memory database. In-memory databases are a feature introduced in later versions of SQL Server, such as SQL Server 2014 and SQL Server 2016. This feature allows for faster data processing by storing data in memory rather than on disk. However, in SQL Server 2008 R2 Enterprise, this feature is not available.

    Rate this question:

  • 19. 

    What is break point in ssis?

    • A. 

      Break point is stopping point in code

    • B. 

      Helps in the transformation of data

    • C. 

      Helps in the extraction of data

    • D. 

      None of the above

    Correct Answer
    A. Break point is stopping point in code
    Explanation
    A breakpoint in SSIS is a stopping point in the code where the execution of the package is paused. It allows developers to pause the execution at a specific point in order to examine the values of variables, check the flow of data, and debug any issues in the package. By setting breakpoints, developers can effectively troubleshoot and analyze the behavior of the package during runtime.

    Rate this question:

  • 20. 

    How many types of ssis connection managers are there?

    • A. 

      4

    • B. 

      5

    • C. 

      19

    • D. 

      6

    Correct Answer
    C. 19
  • 21. 

    You are developing a SQL Server Integration Services (SSIS) package to load data into a SQL Server table on Server A. The package includes a data flow and is executed on ServerB. The destination table has its own identity column. The destination data load has the following requirements:   Ÿ1. The identity values from the source table must be used.Ÿ2. Default constraints on the destination table must be ignored.Ÿ3. Batch size must be 100,000 rows.   You need to add a destination and configure it to meet the requirements. Which destination should you use? 

    • A. 

      SQL Server Destination

    • B. 

      OLE DB Destination with Fast Load

    • C. 

      ADO NET Destination without Bulk Insert

    • D. 

      OLE DB Destination without Fast Load

    Correct Answer
    B. OLE DB Destination with Fast Load
    Explanation
    The OLE DB Destination with Fast Load should be used because it allows for the use of identity values from the source table, ignores default constraints on the destination table, and has the ability to load data in batches of 100,000 rows.

    Rate this question:

  • 22. 

    What do you understand by design limitation in SSIS package?

    • A. 

      Making use of parallelism

    • B. 

      Not making use of parallelism

    • C. 

      Package uses few single-threaded tasks.

    • D. 

      None of the mentioned

    Correct Answer
    B. Not making use of parallelism
    Explanation
    The design limitation in an SSIS package refers to the constraint of not utilizing parallelism. This means that the package is not designed to execute multiple tasks simultaneously, but rather relies on a single-threaded approach. This limitation can impact the performance and efficiency of the package, as it may not be able to take advantage of the available system resources and execute tasks concurrently.

    Rate this question:

  • 23. 

    What conditions can be given to preceednce constraints?

    • A. 

      Constraint

    • B. 

      Expression

    • C. 

      All of the above

    • D. 

      None of the above

    Correct Answer
    C. All of the above
    Explanation
    Precedence constraints can be given various conditions depending on the specific requirements of the task. These conditions can be expressed through constraints, expressions, or a combination of both. Therefore, "All of the above" is the correct answer as it encompasses all possible conditions that can be given to precedence constraints.

    Rate this question:

  • 24. 

    You are creating a SQL Server Integration Services (SSIS) package to retrieve product data from two different sources. One source is hosted in a SQL Azure database. Each source contains products for different distributors. Products for each distributor source must be combined for insertion into a single product table destination. You need to select the appropriate data flow transformation to meet this requirement. Which transformation types should you use?

    • A. 

      Multicast

    • B. 

      Merge Join

    • C. 

      Union All

    • D. 

      Merge

    Correct Answer(s)
    C. Union All
    D. Merge
  • 25. 

    What are the different types of containers in SSIS

    • A. 

      Foreach Loop Container

    • B. 

      For Loop Container

    • C. 

      Sequence Container

    • D. 

      Task Host Container

    Correct Answer(s)
    A. Foreach Loop Container
    B. For Loop Container
    C. Sequence Container
    D. Task Host Container
    Explanation
    The correct answer is a list of different types of containers in SSIS. These containers are used to group and organize tasks in an SSIS package. The Foreach Loop Container is used to iterate over a collection, such as files in a folder. The For Loop Container is used to repeat a set of tasks a specified number of times. The Sequence Container is used to group tasks together and define their execution order. The Task Host Container is a placeholder container used to host individual tasks.

    Rate this question:

  • 26. 

    How many components are present in SSIS Toolbox? 

    • A. 

      7

    • B. 

      6

    • C. 

      5

    • D. 

      4

    Correct Answer
    D. 4
    Explanation
    There are 4 components present in the SSIS Toolbox.

    Rate this question:

  • 27. 

    Which of the following show flow of data from the corresponding sources to the referred destinations

    • A. 

      control flow

    • B. 

      data flow

    • C. 

      Transformation

    • D. 

      None of these

    Correct Answer
    B. data flow
    Explanation
    The correct answer is "data flow" because data flow refers to the movement of data from one source to a specific destination. It involves the transfer of data between different components or systems, ensuring that the data is processed and delivered to the intended recipients. Control flow, on the other hand, refers to the order in which instructions are executed within a program or system. Transformation may involve modifying or converting data, but it does not necessarily involve the movement of data from one source to a destination. Therefore, the answer "data flow" best represents the flow of data from sources to destinations.

    Rate this question:

  • 28. 

    What are different ways to execute your SSIS Package?

    • A. 

      By using BIDS/ SSDT

    • B. 

      DtExecUI

    • C. 

      Dtexec.exe

    • D. 

      SQL Server Agent Job

    • E. 

      Run SSIS Package Programmatically

    • F. 

      Windows Scheduler or Any third party Scheduler

    Correct Answer(s)
    A. By using BIDS/ SSDT
    B. DtExecUI
    C. Dtexec.exe
    D. SQL Server Agent Job
    E. Run SSIS Package Programmatically
    F. Windows Scheduler or Any third party Scheduler
    Explanation
    The different ways to execute an SSIS Package include using BIDS/SSDT, DtExecUI, Dtexec.exe, SQL Server Agent Job, running the package programmatically, and using Windows Scheduler or any third-party scheduler. These methods provide flexibility in executing the SSIS Package based on the specific requirements and environment.

    Rate this question:

  • 29. 

    What Is BCP?

    • A. 

      Bulk copy program.

    • B. 

      Business continuity program

    • C. 

      Both

    • D. 

      None

    Correct Answer
    A. Bulk copy program.
    Explanation
    The correct answer is "Bulk copy program." BCP stands for Bulk copy program, which is a tool used to transfer large amounts of data between databases. It is commonly used for data migration or data loading purposes. The other options, "Business continuity program" and "Both" are incorrect as they do not accurately define what BCP stands for.

    Rate this question:

  • 30. 

    Define Index?

    • A. 

      Predefined pointers to data page

    • B. 

      Primary key

    • C. 

      Foreign Key

    • D. 

      None

    Correct Answer
    A. Predefined pointers to data page
    Explanation
    Index is a predefined data structure that contains pointers to the data pages in a database. These pointers help in efficient retrieval of data by providing quick access to the desired information. By using indexes, the database system can locate the required data without scanning the entire database, resulting in improved performance and reduced query execution time. Therefore, the correct answer is "Predefined pointers to data page".

    Rate this question:

  • 31. 

    What are types of components available in Data flow task?

    • A. 

      Container

    • B. 

      Source

    • C. 

      Destination

    • D. 

      Transformation

    Correct Answer(s)
    B. Source
    C. Destination
    D. Transformation
    Explanation
    The question is asking about the types of components available in a Data Flow task. The correct answer is "Source, Destination, Transformation." In a Data Flow task, a source component is used to retrieve data from a specific data source, a destination component is used to write data to a specific destination, and a transformation component is used to modify or manipulate the data as it flows through the task. These three types of components are essential for building data integration and ETL (Extract, Transform, Load) processes.

    Rate this question:

  • 32. 

    You want to run the subsequent container in the Control flow task only if the preceding container with execute sql task sets a variable with a appropriate value, what type of evaluation option is required?

    • A. 

      Expression

    • B. 

      Constraint OR Expression

    • C. 

      Constraint AND Expression

    • D. 

      All of the listed options

    • E. 

      Constraint

    Correct Answer
    C. Constraint AND Expression
    Explanation
    The correct answer is "Constraint AND Expression". In order for the subsequent container to run only if the preceding container sets a variable with an appropriate value, both a constraint and an expression need to be used. The constraint will ensure that the subsequent container is only executed if the condition is met, and the expression will evaluate the value of the variable to determine if it is appropriate.

    Rate this question:

  • 33. 

    When using an Execute SQL task the developer must ensure that the order of the parameters within the query must be the same as the order in which the parameters appear in the Query. 

    • A. 

      True

    • B. 

      False

    Correct Answer
    A. True
    Explanation
    When using an Execute SQL task, the order of the parameters within the query must be the same as the order in which the parameters appear in the Query. This is because the query is executed based on the order of the parameters, and if they are not in the correct order, the query may not produce the expected results or may fail altogether. Therefore, it is important for the developer to ensure that the parameters are arranged correctly in both the query and the task to ensure the proper execution of the SQL statement.

    Rate this question:

  • 34. 

    How to execute a bat/cmd files from SSIS tasks?

    • A. 

      Execute Package Task

    • B. 

      Execute Process Task

    • C. 

      Execute SQL Task

    • D. 

      None of the listed options

    Correct Answer
    B. Execute Process Task
    Explanation
    The correct answer is Execute Process Task. This task allows you to execute a batch file or a command file from within SSIS. It provides options to specify the file path, arguments, and working directory for the file to be executed. This task is commonly used when you need to run external processes or scripts as part of your SSIS package.

    Rate this question:

  • 35. 

    What are the options to avoid changes to the package by an unknown user?

    • A. 

      EncryptAllWithPassword

    • B. 

      Encrypt

    • C. 

      EncryptWithPwd

    • D. 

      EnablePassword

    Correct Answer
    A. EncryptAllWithPassword
    Explanation
    To avoid changes to the package by an unknown user, the option "EncryptAllWithPassword" is the correct answer. This option suggests encrypting the entire package with a password, ensuring that only authorized users with the password can make changes to the package. This provides an added layer of security and prevents unauthorized access or modifications by unknown users.

    Rate this question:

  • 36. 

    What are the types of Logging available in SSIS?

    • A. 

      Text file Provider

    • B. 

      Windows event Log Provider

    • C. 

      Excel Log provider

    • D. 

      Csv log provider

    Correct Answer(s)
    A. Text file Provider
    B. Windows event Log Provider
    Explanation
    The question is asking for the types of logging available in SSIS. The correct answer includes "Text file Provider" and "Windows event Log Provider." These are two options for logging in SSIS that allow users to log information to a text file or the Windows event log, respectively. The other options listed, "Excel Log provider" and "csv log provider," are not valid types of logging available in SSIS.

    Rate this question:

  • 37. 

    OLEDB connection manager supports 

    • A. 

      MySql

    • B. 

      Oracle

    • C. 

      DB2

    • D. 

      Sql Server

    • E. 

      Teradata

    Correct Answer(s)
    A. MySql
    B. Oracle
    C. DB2
    D. Sql Server
    E. Teradata
    Explanation
    The OLEDB connection manager supports various database systems, including MySql, Oracle, DB2, Sql Server, and Teradata. This means that it can establish connections and interact with these databases using the OLEDB technology.

    Rate this question:

  • 38. 

    Identify the control flow components in the options provided?

    • A. 

      Data Flow Task

    • B. 

      SQL Task

    • C. 

      Merge Transformation

    • D. 

      Lookup Task

    • E. 

      For each Loop containers

    • F. 

      History Cleanup Task

    • G. 

      Derived Column

    Correct Answer(s)
    A. Data Flow Task
    B. SQL Task
    E. For each Loop containers
    F. History Cleanup Task
    Explanation
    The control flow components in the options provided are the Data Flow Task, SQL Task, For each Loop containers, and History Cleanup Task. These components are used to control the flow of execution in a program or workflow. The Data Flow Task is used to extract, transform, and load data between different sources and destinations. The SQL Task is used to execute SQL statements or scripts. The For each Loop containers are used to iterate over a collection of items and perform a set of tasks for each item. The History Cleanup Task is used to clean up historical data.

    Rate this question:

  • 39. 

    You need to control the execution behavior of the package by manipulating the SuccessValue of the task, When can this manipulation can  take affect?

    • A. 

      FailTaskIfReturnCodeIsNotSuccessValue

    • B. 

      None of the listed options

    • C. 

      ReturnErrorCode

    • D. 

      FailTaskCodeIsNotSuccessValue

    Correct Answer
    A. FailTaskIfReturnCodeIsNotSuccessValue
    Explanation
    The manipulation of the SuccessValue can take effect when the task fails to return the success value. In this case, the task will fail if the return code is not equal to the SuccessValue.

    Rate this question:

  • 40. 

    The column in a table is set to read only then can we create a new column using derived column?

    • A. 

      True

    • B. 

      False

    Correct Answer
    A. True
    Explanation
    If a column in a table is set to read-only, it means that it cannot be modified directly. However, creating a new column using a derived column is still possible. Derived columns are calculated based on the values of other columns, so they do not directly modify the read-only column. Therefore, it is true that we can create a new column using derived columns even if the original column is set to read-only.

    Rate this question:

  • 41. 

    Default Isolation level in SSIS?

    • A. 

      Read Uncommitted

    • B. 

      Read Committed

    • C. 

      Serializable

    • D. 

      Snapshot

    Correct Answer
    C. Serializable
    Explanation
    The default isolation level in SSIS is Serializable. This means that transactions are executed in a way that ensures data consistency by preventing other transactions from accessing the data until the current transaction is completed. This ensures that the data remains unchanged and consistent during the transaction process.

    Rate this question:

  • 42. 

    What Other sources can be downloaded in Microsoft website?

    • A. 

      Orcale

    • B. 

      Teradata

    • C. 

      SAPBI

    • D. 

      My sql

    Correct Answer(s)
    A. Orcale
    B. Teradata
    C. SAPBI
    D. My sql
  • 43. 

    Merge join works as 

    • A. 

      Left right full

    • B. 

      Self right outer

    • C. 

      Inner left full

    • D. 

      Right outer join

    Correct Answer
    C. Inner left full
    Explanation
    Merge join works as an inner join that returns only the matching rows from both the left and right tables. It combines the rows from both tables based on the specified join condition, discarding any unmatched rows. The result set includes all columns from both tables.

    Rate this question:

  • 44. 

    The total Number of container tasks available in SSIS is

    • A. 

      2

    • B. 

      4

    • C. 

      1

    • D. 

      3

    Correct Answer
    B. 4
    Explanation
    The correct answer is 4 because SSIS (SQL Server Integration Services) allows the creation and management of container tasks, which are used to group and organize other tasks. These container tasks can be used to control the flow of data and operations within a package. The question asks for the total number of container tasks available in SSIS, and the correct answer is 4.

    Rate this question:

  • 45. 

    You are operating a SQL Server 2008 Integration Services (SSIS) instance through Business Intelligence Development Studio. Utilizing for Company.com, there is a common data flow component in the package which implements normally. You try to increase one sample of the common information flow component to the package. But the component could not show in thetoolbox . You should make sure that you should increase the common data flow component to the toolbox. Which is the correct answer? 

    • A. 

      You should duplicate the assembly to the proper folder.

    • B. 

      You should increase the assembly to the default Assembly Cache.

    • C. 

      You should recompile the assembly through utilizing the Assembly Registration Tool

    • D. 

      You should recompile the assembly through utilizing the SYSTEM32 utility.

    Correct Answer
    A. You should duplicate the assembly to the proper folder.
    Explanation
    The correct answer is "You should duplicate the assembly to the proper folder." This means that in order to make the common data flow component appear in the toolbox, you need to copy the assembly file to the correct folder where the SQL Server 2008 Integration Services (SSIS) instance is located. By doing this, the component will be recognized and displayed in the toolbox for you to use.

    Rate this question:

  • 46. 

    You are creating a SQL Server 2008 Integration Services (SSIS) instance for Company.com. The package is listed below: You should make sure that you create a parameter which includes important information that is only useful to the Data Flow Task and the Execute SQL Task and so on. Which is the correct answer? 

    • A. 

      You should develop the parameter at the default scope.

    • B. 

      You should develop the parameter at the Control Flow Job scope.

    • C. 

      You should develop the parameter at the Running SQL Job scope.

    • D. 

      You should develop the parameter at the Sequence Container scope.

    Correct Answer
    D. You should develop the parameter at the Sequence Container scope.
  • 47. 

    How you can notify the staff members about package failure?

    • A. 

      Send Mail Task

    • B. 

      Notification from SQL Agent

    • C. 

      Can’t send notification automatically

    Correct Answer(s)
    A. Send Mail Task
    B. Notification from SQL Agent
    Explanation
    The correct answer is "Send Mail Task." This is because the Send Mail Task allows you to send an email notification to the staff members about a package failure. The Notification from SQL Agent is not a suitable option as it is mentioned that it cannot send notifications automatically.

    Rate this question:

  • 48. 

    You are creating a SQL Server 2008 Integration Services (SSIS) package for Company.com. In order to specify the XML configuration during package development, you should apply the configuration to the deployed package. Which is the correct answer? 

    • A. 

      You should utilize the Publish utility.

    • B. 

      You should utilize the default command line utility.

    • C. 

      You should utilize the exec command line utility.

    • D. 

      You should utilize the DTEXEC command line utility.

    Correct Answer
    D. You should utilize the DTEXEC command line utility.
    Explanation
    The DTEXEC command line utility is used to execute SSIS packages and allows for the specification of XML configuration during package development. This utility provides a way to pass configuration values to the package at runtime, allowing for flexibility and customization. Utilizing the DTEXEC command line utility is the correct approach for specifying XML configuration in this scenario.

    Rate this question:

  • 49. 

    You administer an instance of a SQL Server 2008 server. The server is used to execute SQL Server 2008 Integration Services (SSIS) packages. You need to ensure that the server executes only correctly signed packages.   What should you do?

    • A. 

      Set the package protection level on all packages to DontSaveSensitive.

    • B. 

      Set the BlockedSignatureStates registry entry to Block invalid and untrusted signatures and unsigned packages

    • C. 

      Set the package protection level on all packages to EncryptSensitiveWithPassword.

    • D. 

      Set the BlockedSignatureStates registry entry to NoAdministrativeRestriction

    Correct Answer
    B. Set the BlockedSignatureStates registry entry to Block invalid and untrusted signatures and unsigned packages
    Explanation
    Setting the BlockedSignatureStates registry entry to "Block invalid and untrusted signatures and unsigned packages" will ensure that the server executes only correctly signed packages. This setting will block any packages that have invalid or untrusted signatures, as well as unsigned packages. By enforcing this restriction, the server will only execute packages that have been properly signed and verified, ensuring the integrity and security of the execution process.

    Rate this question:

  • 50. 

    You are in the process of creating a SQL Server 2008 Integration Services (SSIS) data flow. Every source database is belongs to a different server and based on a SELECT statement which utilizes an ORDER BY clause. The data flow is listed below: You get error message below when you connect the paths from the two sources "Data Flow Task: The input is not sorted. The 'input "Merge Join Left Input" (80)' must be sorted." You should make sure that data from every search is contained in every row in the data flow. In order to solve the problem, which is the correct answer? 

    • A. 

      You should increase a Lookup transformation for some data flows.

    • B. 

      You should increase a Lookup transformation for every data flow.

    • C. 

      You should refresh the sorting properties in each source component.

    • D. 

      You should choose a default transformation

    Correct Answer
    C. You should refresh the sorting properties in each source component.
    Explanation
    In order to solve the error message "The input is not sorted" and ensure that data from every source is contained in every row in the data flow, the correct answer is to refresh the sorting properties in each source component. This means that the ORDER BY clause in the SELECT statement should be properly configured and applied in each source component to ensure that the data is sorted correctly before it is merged in the data flow.

    Rate this question:

Back to Top Back to top
×

Wait!
Here's an interesting quiz for you.

We have other quizzes matching your interest.