SQL Server Integration Services (SSIS) Quiz

Posted on: | By: – Comments Off

SSIS Expert Quiz

SSIS – SQL Server Integration Services :This SSIS Expert Quiz contains set of 60 SSIS Quiz which will help to clear any exam which is designed for Expert.



1) Which are the correct statements with respect to SSISDB Catalog?
Option A) Each instance of SQL Server can have one catalog
Option B) Each instance of SQL Server can have multiple catalog
Option C) Each catalog can have zero or more folders
Option D) Each folder can have zero or more projects and zero or more environments

  1. Option A,C&D
  2. Option B,C &D
  3. Option A&C
  4. Option B&C

Answer : A

 
 
2) During development phase the protection level of packages is set to the default value which is EncryptSensitiveWithUserKey, Now the package needs to deployed into production and operations team is performing the deployment and once deployed the operations team only should have the access. What is required to achieve this?

  1. No change is required with the protection level of the packages
  2. Dev team needs to change the protection level EncryptSensitiveWithPassword and share with Operations team. Once deployed Operations team change the protection level
  3. Default Protection level EncryptSensitiveWithUserKey can be changed by Operations team by themselves since they have the admin privileges
  4. Request the developer to login with his credentials and help the operations team with the deployment

Answer : B

 
 
3) ___________ Protection Level set or Uses a key that is based on the current user profile to encrypt the whole package. Only the user who created or exported the package can open the package in SSIS Designer or run the package by using the dtexec command prompt utility.

  1. EncryptSensitiveWithPassword
  2. EncryptSensitiveWithUserKey
  3. EncryptAllWithUserKey
  4. EncryptAllWithPassword

Answer : C

 
 
4) Once the development of SSIS packages are completed and require to automate the execution of packages with SQL Server Agent. Pls specify what roles\roles provide permissions to Execute All the packages with SQL Server Agent
Option A) db_ssisadmin
Option B) db_ssisltduser
Option C) db_ssisoperator

  1. Option A&B
  2. Option B&C
  3. Option A&C
  4. Option A

Answer : C

 
 
5) Which one is not a standard SQL Server Integration Services Role

  1. db_ssisadmin
  2. db_ssisltduser
  3. db_ssisoperator
  4. db_ssisreadonly

Answer : D

 
 
6) Which action is not supported by db_ssisltduser role?

  1. Enumerate all packages.
  2. View all packages.
  3. View own packages.
  4. Execute own packages.

Answer : B

 
 
7) When you configure a package to use configurations, checkpoints, and logging, the package stores this information outside the package. The information that is stored outside the package might contain sensitive data, while securing data which option is an incorrect one.

  1. To protect configurations that the package saves to SQL Server database tables and use SQL Server security features.
  2. To protect logs that the package saves to SQL Server database tables, use SQL Server security features.
  3. To control access to files, use the access control lists (ACLs)
  4. Set Protection Level to secure the sensitive data

Answer : D

 
 
8) Which feature is supported SQL Server Integration Services 2012

  1. Visual Studio Tools for Applications (VSTA)
  2. The options for connecting shapes on the design surface of the Control Flow tab and Data Flow tab
  3. The options to configure a data viewer to display data in a histogram, scatter plot, or column chart
  4. Data Source Views

Answer : A

 
 
9) Customer is in the process of migrating SSIS packages built with 2005 to 2012 and some of the packages have Active X Scripts, when upgrading packages what is expected output and choose the right option

  1. SSIS packages are upgraded with out any issues
  2. Need to rewrite ActiveX Script functionality
  3. Use SSIS package upgrade wizard
  4. Partial upgrade is possible

Answer : B

 
 
10) Which control flow task support to execute XMLA commands

  1. Analysis Services Execute DDL Task
  2. Data Flow Task
  3. File System Task
  4. Execute Process Task

Answer : A




 
11) While configuring Analysis Services Execute DDL Task and there are options available to specify the connection and Source Type. Choose the INVALID source type to specify the DDL Statements

  1. Direct Input
  2. File Connection
  3. Variable
  4. SQL Connection

Answer : D

 
 
12) What does XMLA stands, which is related SSAS objects

  1. XML for Analytics
  2. XML for Analysis
  3. XML for Adhoc Analysis
  4. XML for Addition

Answer : B

 
 
13) Task runs prediction queries based on models built in Analysis Services

  1. Data Mining Query Task
  2. Analysis Services Execute DDL Task
  3. Analysis Services Processing Task
  4. Data Profiling Task

Answer : A

 
 
14) Back Up Database task supports various recovery models and backups , which is NOT a standard supported recovery model and backup types by Back Up Database Task

  1. Simple
  2. Bulk-Logged
  3. Full
  4. Partial

Answer : D

 
 
15) Need to design a package to remove the backup files or maintenance plan reports on the specified server and also to remove a specific file or remove a group of files in a folder, Which tasks supports this implementation?

  1. History Cleanup Task
  2. Maintenance Cleanup Task
  3. Rebuild Index Task
  4. Shrink Database Task

Answer : B

 
 
16) Which Task helps to reduce the size of SQL Server database data and log files

  1. Execute T-SQL Statement Task
  2. Update Statistics Task
  3. Maintenance Cleanup Task
  4. Shrink Database Task

Answer : D

 
 
17) To configure the Data Flow task for better performance, you can configure the task’s properties and modify the DefaultBufferSize, What is the default value set for the buffer size

  1. 10 MB
  2. 100 MB
  3. 1000 MB
  4. 10000 MB

Answer : A

 
 
18) To configure the Data Flow task for better performance, you can configure the task’s properties and modify the DefaultBufferMaxRows, What is the default value set for maximum number of rows

  1. 10 rows
  2. 100 rows
  3. 1000 rows
  4. 10000 rows

Answer : D

 
 
19)
Parallel execution improves performance on computers that have multiple physical or logical processors. To support parallel execution Integration Services uses few property\properties to improve performance. Choose the correct option\s
a) EngineThreads
b) DefaultBufferSize
c) MaxConcurrentExecutables
 

  1. Option A&B
  2. Option B&C
  3. Option A&C
  4. Option A,B&C

Answer : C

 
 
20) The data flow engine begins the task of sizing its buffers by calculating the estimated size of a single row of data. Then it multiplies the estimated size of a row by the value of DefaultBufferMaxRows to obtain a preliminary working value for the buffer size.  Which statement is INCORRECT with respect to adjusting the size of buffers

  1. If the result is more than the value of DefaultBufferSize, the engine reduces the number of rows
  2. If the result is less than the internally-calculated minimum buffer size, the engine increases the number of rows
  3. If the result falls between the minimum buffer size and the value of DefaultBufferSize, the engine sizes the buffer as close as possible to the estimated row size times the value of DefaultBufferMaxRows
  4. When sufficient memory is available, you should use larger number of small buffers

Answer : D




 
21) To configure individual data flow components for better performance, there are some general guidelines that you can follow. There are also specific guidelines for each type of data flow component: source, transformation, and destination. Choose the option which DOESN’t help to improve performance

  1. Set the IsSorted property on the output of an upstream data flow component to True
  2. Performing the sort operation in the data flow tasks enhances the performance
  3. Specify the Data Flow task runs in optimized mode (RunInOptimizedMode property)
  4. When you use an OLE DB source to retrieve data from a view, select “SQL command” as the data access mode and enter a SELECT statement

Answer : B

 
 
22) Source tables change over time. A data mart or data warehouse that is based on those tables needs to reflect these changes. However, a process that periodically copies a snapshot of the entire source consumes too much time and resources. SQL Server  has inbuilt feature which is supported from SQL Server 2008 version. Identify the process or feature?

  1. Include timestamp columns in the database tables
  2. Define triggers in the database
  3. Include a script or process to identify the changes
  4. Enable Change Data Capture feature in the database

Answer : D

 
 
23) When implementing Change Data Capture using SQL Server Integration Services there are few steps in creating the packages and identify the step which involves the Data Flow Task?

  1. Retrieving and Understanding the Change Data
  2. Specifying an Interval of Change Data
  3. Determining Whether the Change Data Is Ready
  4. Preparing to Query for the Change Data

Answer : A

 
 
24) When implementing Change Data Capture using SQL Server Integration Services there are few steps in creating the packages and identify the step which involves the Control Flow Task?

  1. Retrieving and Understanding the Change Data
  2. Determining Whether the Change Data Is Ready
  3. Processing Inserts, Updates, and Deletes
  4. Applying the Changes to the Destination

Answer : B

 
 
25) What is NOT TRUE with respect to minimally logged operations

  1. It keep track of extent allocations and metadata changes only
  2. Is often faster than a fully logged operation if logging is the bottleneck
  3. Fewer writes go the transaction log, a much smaller log file with a lighter I/O requirement
  4. Cannot participate in a transaction

Answer : D

 
 
26) Which is a command- D34line utility tool for performing bulk load

  1. BULK INSERT
  2. BCP
  3. Integration Services Data Destinations to perform bulk load
  4. SELECT INTO method to perform bulk load

Answer : B

 
 
27) Which is fastest method to bulk load data where the integration services runs in the same instance of SQL Server

  1. Using Integration Services with SQL Server Destination
  2. Integration Services with OLE DB Destination
  3. Using BULK Insert command
  4. BCP

Answer : A

 
 
28) If your source data is a table inside SQL Server, which options is a quick and easy one to achieve Bulk Loading an Empty, NonPartitioned Table

  1. Using BCP
  2. Using SELECT INTO or INSERT … SELECT
  3. Using BULK Insert command
  4. Using Integration Services Data Destinations

Answer : B

 
 
29) What are the different package configuration types supported by Integrations Services
Option A) XML Configuration File
Option B) Text File
Option C) Environment Variable
Option D Registry Entry

  1. Option A,B&C
  2. Option B,C &D
  3. Option A,C&D
  4. Option A,B,C&D

Answer : C

 
 
30) During Bulk Load there are typical waits, which are required to be gathered to analyze the root cause to further investigate and eliminate the bottlenecks. In such bulk loading scenario it is observed that the “input data is too slow” and to verify this which is Wait Type stats data needs to be collected for analysis

  1. PAGEIOLATCH_<X>
  2. PREEMPTY_COM_<X>
  3. OLEDB
  4. ASYNC_NETWORK_IO

Answer : C




 
31) During Bulk Load there are typical waits, which are required to be gathered to analyze the root cause to further investigate and eliminate the bottlenecks. In such bulk loading scenario it is observed that the “Network cannot keep up” and to verify this which is Wait Type stats data needs to be collected for analysis

  1. IMPROV_IO
  2. ASYNC_NETWORK_IO
  3. WRITELOG
  4. PAGELATCH_UP

Answer : B

 
 
32) Which Performance Object Counter helps to Measure the bandwidth you are getting from the NICs in the server

  1. Bytes Total / sec
  2. Bulk copy rows / sec
  3. Log bytes Flushed / Sec
  4. Disk Read Bytes / sec

Answer : A

 
 
33) Which Performance Object Counter helps to Measure the number of rows coming into the database

  1. Disk Read Bytes / sec
  2. % Processor time – Total
  3. Bulk copy rows / sec
  4. Bytes Total / sec

Answer : C

 
 
34) When installing SQL Server Integration Services engine on 64-bit development environment, Which directory path under which the 64-bit features are installed

  1. Program Files (x86)
  2. Program Files
  3. Program Files (32)
  4. Program Files (64

Answer : B

 
 
35) With SQL Server 2012 and above Project Deployment Model options is available and which is NOT a applicable statement to Project Deployment Model

  1. CLR integration is required on the DB engine
  2. Package is the Unit of deployment
  3. Project deployment file is referred with .ispac extension
  4. Packages are parameters are deployed to SSIS Catalog

Answer : B

 
 
36) Which of the following is used to enumerate the result set of an XML

  1. Foreach Item
  2. Foreach Nodelist
  3. Foreach ADO.NET
  4. Foreach SMO

Answer : B

 
 
37) which of the following task provide structures in packages

  1. precedence constraints that connect the executables
  2. Containers
  3. Transformations connected through paths
  4. Annotations

Answer : B

 
 
38) Which of the following is not a valid case while developing a package

  1. you cannot use a foreach loop container inside another for each loop container
  2. you cannot call the dataflow task as the first task in a package
  3. you should atleast have minimum two tasks in a package
  4. None of the above

Answer : D

 
 
39) Which of the following is a Maintenance task for SSIS administrators

  1. Transfer Database Task
  2. Execute T-SQL Statement Task
  3. Transfer Master Stored Procedures Task
  4. Execute Process Task

Answer : B

 
 
40) Which of the following information is not present in the Metadata for external columns

  1. name
  2. datatype
  3. length
  4. value

Answer : D




 
41) An error output for a source component contains

  1. Same columns as the regular output
  2. Only error information columns
  3. Same columns as the regular output and error information columns
  4. No error output available in source component

Answer : C

 
 
42) If you want to load the country details stored in a flat file into sql server and oracle databases, which transformation will you use

  1. conditional split transformation
  2. lookup transformation
  3. multicast transformation
  4. merge join transformation

Answer : C

 
 
43) “The Slowly Changing Dimension transformation directs these rows to an output named Changing Attributes Updates Output.” What type of SCD is supported here

  1. Type 1
  2. Type 2
  3. Type 3
  4. Type 4

Answer : A

 
 
44) Which type is not supported by Slowly Changing Dimension transformation

  1. Type 1
  2. Type 2
  3. Type 3
  4. Type 4

Answer : C

 
 
45) The “Inferred Member Updates Output” – Output of the Slowly Changing Dimension transformation is used to

  1. Update the Dimension data which is already referred by fact
  2. Update the dimension key in fact table
  3. Used to insert the new dimension record
  4. Used to insert new fact record

Answer : A

 
 
46) Fuzzy Lookup transformation requires a reference table is

  1. Only Sql Server
  2. Any RDBMS
  3. Only Files
  4. Fuzzy Lookup transformation does not require any reference table

Answer : A

 
 
47) Fuzzy Lookup transformation creates a new table for

  1. match lookup data
  2. match index table
  3. exact match data
  4. mismatch data

Answer : B

 
 
48) What is the range of the similarity score in fuzzy grouping transformation

  1. 0 to 1
  2. 0 to 100
  3. 1 to 10
  4. 1,2,3,4

Answer : A

 
 
49) To identify duplicate rows, what similarity threshold will you set in fuzzy group

  1. closer to 1
  2. closer to 0
  3. closer to 100
  4. none of the above

Answer : A

 
 
50) The Term Extraction transformation extracts terms from

  1. Any language text data
  2. English text
  3. Unicode Data
  4. All of the above

Answer : B




 
51) If an apostrophe is in a word like “bicycle’s”, the Term Extraction Transformation will output this word as

  1. bicycle
  2. bicycles
  3. bicycle’s
  4. Term Extraction Transformation cannot identify a word with apostrophe

Answer : A

 
 
52) If you want to mine the highest number of tweets you made about a person from your tweet archive.

  1. Term Extraction Transformation
  2. Fuzzy Lookup Transformation
  3. Fuzzy Grouping Transformation
  4. Term Lookup Transformation

Answer : A

 
 
53) To find the number of times a term in the lookup table occurs in a input column, which transformation is used

  1. Term Extraction Transformation
  2. Fuzzy Lookup Transformation
  3. Fuzzy Grouping Transformation
  4. Term Lookup Transformation

Answer : D

 
 
54) The lookup table used in Term Lookup Transformation must be a table in

  1. Sql Server
  2. Sql Server or Access
  3. RDBMS
  4. file

Answer : B

 
 
55) Data Mining Query Transformation is used to

  1. T-Sql Queries against data mining models
  2. DMX queries against data mining models
  3. T-Sql Queries against OLTP Table and data mining models
  4. All of the above

Answer : B

 
 
56) One Data Mining Query Transformation  can execute multiple prediction queries if

  1. The models are built on the different data mining structure
  2. The models are built on the same data mining structure
  3. Data Mining Query Transformation can not execute multiple prediction queries
  4. both a & b

Answer : B

 
 
57) The Pivot transformation is used to make

  1. Remove duplicates in demoralized data set
  2. Demoralized data set into a normalized data set
  3. Normalized data set into a demoralized data set
  4. none of the above

Answer : C

 
 
58) Which Transformation can be used to transpose  rows to columns

  1. PIVOT Transformation
  2. UNPIVOT Transformation
  3. DERIVED COLUMN Transformation
  4. Can’t use a transformation, use T-sql instead

Answer : B

 
 
59) Consider a table containing employee’s salary increment data stored with the corresponding year in Year column, If you want to get the Salary Increment data for each employee in a single row for every year as separate columns, which  PivotUsage property you need to set in PIVOT transformation

  1. 0
  2. 1
  3. 2
  4. 3

Answer : B

 
 
60) An error output for a source component contains

  1. PIVOT Transformation
  2. UNPIVOT Transformation
  3. Both
  4. None

Answer : B




 
 

Related Articles

Advanced Manufacturing Process Quiz

Web analytics, Adobe Analytics, Google Analytics Quiz

SailPoint Quiz