SSIS Beginner Quiz

Posted on: | By: – Comments Off

SSIS Beginner Quiz




1
Which is NOT a standard Built-In Connection Manager

  1. OLEDB
  2. DQS
  3. TERADATA
  4. SMTP

[bg_collapse view=”button-green” color=”#FFF” icon=”arrow” expand_text=”Show Answer” collapse_text=”Hide Answer” ]Answer : TERADATA[/bg_collapse]
 
2
Which is not a standard Data Flow Transformation Task

  1. Percentage Sampling
  2. Derived Column
  3. Script Task
  4. Script Component

[bg_collapse view=”button-green” color=”#FFF” icon=”arrow” expand_text=”Show Answer” collapse_text=”Hide Answer” ]Answer : Script Task[/bg_collapse]
 
3
Which of the following is the data flow component that writes the data from a data flow to a specific data store, or creates an in-memory dataset.

  1. Source
  2. Destination
  3. Path
  4. Transformation

[bg_collapse view=”button-green” color=”#FFF” icon=”arrow” expand_text=”Show Answer” collapse_text=”Hide Answer” ]Answer : Transformation[/bg_collapse]
 
4
Scenario is to use the Scripts for work that must be done once in a package (or once per enumerated object), instead than once per data row. Choose the right option

  1. Script Task
  2. Send Mail Task
  3. Script Component
  4. SSIS Execute SQL Task

[bg_collapse view=”button-green” color=”#FFF” icon=”arrow” expand_text=”Show Answer” collapse_text=”Hide Answer” ]Answer : Script Task[/bg_collapse]
 
5
Find out the Control Flow task

  1. Derived column
  2. OLEDB Command
  3. SORT command
  4. XML Task

[bg_collapse view=”button-green” color=”#FFF” icon=”arrow” expand_text=”Show Answer” collapse_text=”Hide Answer” ]Answer : XML Task[/bg_collapse]
 
6
Find out the data flow task from the below component

  1. Lookup Task
  2. Execute SQL Task
  3. Bulk Insert Task
  4. Send Mail Task

[bg_collapse view=”button-green” color=”#FFF” icon=”arrow” expand_text=”Show Answer” collapse_text=”Hide Answer” ]Answer : Lookup Task[/bg_collapse]
 
7
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

[bg_collapse view=”button-green” color=”#FFF” icon=”arrow” expand_text=”Show Answer” collapse_text=”Hide Answer” ]Answer : Option A,C&D[/bg_collapse]
 
8
Which Integration Services Container support to Run a control flow repeatedly by using an enumerator?

  1. For Loop Container
  2. Foreach Loop Container
  3. Task Host Container
  4. Sequence Container

[bg_collapse view=”button-green” color=”#FFF” icon=”arrow” expand_text=”Show Answer” collapse_text=”Hide Answer” ]Answer : Foreach Loop Container[/bg_collapse]
 
9
Which container groups and manage projects.

  1. For each container
  2. Package
  3. Data Flow Task
  4. Solution

[bg_collapse view=”button-green” color=”#FFF” icon=”arrow” expand_text=”Show Answer” collapse_text=”Hide Answer” ]Answer : Solution[/bg_collapse]



10
Which is NOT a correct statement with respect to debugging the script task

  1. If you execute only the individual task, breakpoints in the Script task code are ignored
  2. You must execute the package to debug into your Script task
  3. You can debug a Script task when you run the Script task as part of a child package that is run from an Execute Package task
  4. To debug the code in your Script task, set at least one breakpoint in the code

[bg_collapse view=”button-green” color=”#FFF” icon=”arrow” expand_text=”Show Answer” collapse_text=”Hide Answer” ]Answer : You can debug a Script task when you run the Script task as part of a child package that is run from an Execute Package task[/bg_collapse]
 
11
With SQL Server 2012 and above Project Deployment Model options is available and which is NOT a feature of 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

[bg_collapse view=”button-green” color=”#FFF” icon=”arrow” expand_text=”Show Answer” collapse_text=”Hide Answer” ]Answer : Package is the Unit of deployment[/bg_collapse]
 
12
Evaluate the result of the expression deals with the date columns
!(ISNULL(SellStartDate)) && !(ISNULL(SellEndDate)) ? (DT_WSTR,2)DATEDIFF(“mm”,SellStartDate,SellEndDate) : “Unknown”

  1. Return Unknown when the SellStartDate is NULL
  2. Return Unknown when the SellEndDate is NULL
  3. Return Unknown when both the SellStartDate and SellEndDate are NULL
  4. Calculates the no of months when either the SellStartDate or SellEndDate is NULL

[bg_collapse view=”button-green” color=”#FFF” icon=”arrow” expand_text=”Show Answer” collapse_text=”Hide Answer” ]Answer : Return Unknown when both the SellStartDate and SellEndDate are NULL[/bg_collapse]
 
13
Need to develop a package to transfer objects like Partition Functions, Partitions Schema, Assemblies  between instance of SQL Server Databases. Which task is used to perform this operation

  1. Transfer SQL Server Objects
  2. FTP Task
  3. Transfer Database Task
  4. XML Task

[bg_collapse view=”button-green” color=”#FFF” icon=”arrow” expand_text=”Show Answer” collapse_text=”Hide Answer” ]Answer : Transfer SQL Server Objects[/bg_collapse]
 
14
Scenario is to distribute a report, which is generated dynamically by invoking a Visual Basic Application, Which tasks can be leveraged to generate the report?

  1. Execute Package Task
  2. WMI Event Watcher Task
  3. Execute Process Task
  4. File System Task

[bg_collapse view=”button-green” color=”#FFF” icon=”arrow” expand_text=”Show Answer” collapse_text=”Hide Answer” ]Answer : Execute Process Task[/bg_collapse]
 
15
When tasks in a package workflow succeed or fail and to notify users with an E-Mail. With SendMailTask which is NOT the correct option to specify the MessageSourceType

  1. Direct input
  2. OLE DB Connection
  3. File Connection
  4. Variable

[bg_collapse view=”button-green” color=”#FFF” icon=”arrow” expand_text=”Show Answer” collapse_text=”Hide Answer” ]Answer : OLE DB Connection[/bg_collapse]
 
16
With the Data Profiling Task Profiles, Which profile helps to report a set of regular expressions that cover the specified percentage of values in a string column.

  1. Column Value Distribution Profile
  2. Column Statistics Profile
  3. Column Pattern Profile
  4. Column Length Distribution Profile

[bg_collapse view=”button-green” color=”#FFF” icon=”arrow” expand_text=”Show Answer” collapse_text=”Hide Answer” ]Answer : Column Pattern Profile[/bg_collapse]
 
17
Data Profiling task key benefits are

  1. Analyze the source data more effectively
  2. Understand the source data better
  3. Prevent data quality problems
  4. All of the above.

[bg_collapse view=”button-green” color=”#FFF” icon=”arrow” expand_text=”Show Answer” collapse_text=”Hide Answer” ]Answer : All of the above.[/bg_collapse]
 
18
____________ sequentially link tasks in a package

  1. Order Constraints
  2. Precedence Constraints
  3. Priority Constraints
  4. Sequence Constraints

[bg_collapse view=”button-green” color=”#FFF” icon=”arrow” expand_text=”Show Answer” collapse_text=”Hide Answer” ]Answer : Precedence Constraints[/bg_collapse]
 
19
____________ use to quickly load large amounts of data into a SQL Server table or view

  1. Execute SQL Task
  2. Bulk Insert Task
  3. Copy SQL Server Objects Task
  4. Transfer Database Task

[bg_collapse view=”button-green” color=”#FFF” icon=”arrow” expand_text=”Show Answer” collapse_text=”Hide Answer” ]Answer : Bulk Insert Task[/bg_collapse]



20
Which are the Connection Managers supported by Lookup Transformation
Option A) ADO Connection Manager
Option B) Cache Connection Manager
Option C) OLE DB Connection Manager

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

[bg_collapse view=”button-green” color=”#FFF” icon=”arrow” expand_text=”Show Answer” collapse_text=”Hide Answer” ]Answer : Options B & C[/bg_collapse]
 
21
Requirement is to write an output data to a file to use by other system in a shared folder path. And below are options set
– Append SET  to False
– Truncate SET to True
When Export Column Transformation executed and a file already exists in the specified

  1. The transformation creates a new file and writes the data to the file.
  2. The transformation fails design time validation
  3. A run-time error occurs
  4. The transformation deletes and re-creates the file and writes the data to the file.

[bg_collapse view=”button-green” color=”#FFF” icon=”arrow” expand_text=”Show Answer” collapse_text=”Hide Answer” ]Answer : A run-time error occurs[/bg_collapse]
 
22
There is huge volume of data and it is required only the subset of data to test or troubleshoot the problem, What options available
Option A) Row Sampling Transformation
Option B) Conditional Split Transformation
Option C) Percentage Sampling Transformati

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

[bg_collapse view=”button-green” color=”#FFF” icon=”arrow” expand_text=”Show Answer” collapse_text=”Hide Answer” ]Answer : Option A&C[/bg_collapse]
 
23
In which scenarios Merge Join is used than the Union All transformation

  1. The transformation inputs are not sorted
  2. The transformation has more than two inputs.
  3. Combines two sorted datasets into a single dataset
  4. The combined output does not need to be sorted

[bg_collapse view=”button-green” color=”#FFF” icon=”arrow” expand_text=”Show Answer” collapse_text=”Hide Answer” ]Answer : Combines two sorted datasets into a single dataset[/bg_collapse]
 
24
Using which task we can create new column separately in source records?

  1. Conditional Split
  2. Export Column Task
  3. Derived Column Task
  4. Lookup Task

[bg_collapse view=”button-green” color=”#FFF” icon=”arrow” expand_text=”Show Answer” collapse_text=”Hide Answer” ]Answer : Derived Column Task[/bg_collapse]
 
25
There is a performance issue when using the Aggregate Transformation and computing the total sales by country. While grouping by country and aggregating sales, which property helps to improve performance in this scenario where the exact no country list do

  1. Keys
  2. KeysScale
  3. CountDistinctKeys
  4. CountDistinctScale

[bg_collapse view=”button-green” color=”#FFF” icon=”arrow” expand_text=”Show Answer” collapse_text=”Hide Answer” ]Answer : Keys[/bg_collapse]
 
26
_________ Use to copy data directly from source to destination columns, without any transformations applied to the data

  1. Read File
  2. Copy Column
  3. Copy SQL Server Objects
  4. Copy Data

[bg_collapse view=”button-green” color=”#FFF” icon=”arrow” expand_text=”Show Answer” collapse_text=”Hide Answer” ]Answer : Copy Column[/bg_collapse]
 
27
Scenario is to produce an output of sales summary grouped at a country region level and also require to extent the base data with lookup values and derive a column before loaded into 2 different destinations. Which transformation support this functionalit

  1. Conditional Split Transformation
  2. Aggregate Transformation
  3. Lookup Transformation
  4. Multicast Transformation

[bg_collapse view=”button-green” color=”#FFF” icon=”arrow” expand_text=”Show Answer” collapse_text=”Hide Answer” ]Answer : Multicast Transformation[/bg_collapse]
 
28
With the Data Conversion Transformation, which one is supported

  1. This transformation has one or more input, one output, and one error output.
  2. This transformation has one input, one output, and one error output.
  3. This transformation has one or more input, one or more output, and one or more error output.
  4. This transformation has one input, one output, and no error output.

[bg_collapse view=”button-green” color=”#FFF” icon=”arrow” expand_text=”Show Answer” collapse_text=”Hide Answer” ]Answer : This transformation has one input, one output, and one error output.[/bg_collapse]
 
29
Where does the SQL Server Log Provide Writes the log entries to?

  1. SSISDB catalog
  2. sysssislog
  3. sysssispackages
  4. sysssispackagefolders

[bg_collapse view=”button-green” color=”#FFF” icon=”arrow” expand_text=”Show Answer” collapse_text=”Hide Answer” ]Answer : sysssislog[/bg_collapse]



30
Which log provider helps to trace and writes the data to file with .trc extension

  1. SQL Server
  2. Windows Event Log
  3. SQL Server Profiler
  4. XML File

[bg_collapse view=”button-green” color=”#FFF” icon=”arrow” expand_text=”Show Answer” collapse_text=”Hide Answer” ]Answer : SQL Server Profiler[/bg_collapse]
 
31
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
Option

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

[bg_collapse view=”button-green” color=”#FFF” icon=”arrow” expand_text=”Show Answer” collapse_text=”Hide Answer” ]Answer : Option A&C[/bg_collapse]
 
32
Customer wants to protect the entire packages and unauthorized users shouldn’t be allowed to open the package in SSIS Designer or execute the package with dtexec command prompt utility. What is the Protection level required to be set?
Option A) EncryptAllWithPassword
Option B) EncryptSensitiveWithPassword
Option C) EncryptAllWithUserKey

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

[bg_collapse view=”button-green” color=”#FFF” icon=”arrow” expand_text=”Show Answer” collapse_text=”Hide Answer” ]Answer : Option A&C[/bg_collapse]
 
33
Which file contains information that Business Intelligence Development Studio(BIDS)/SQL Server Data Tools (SSDT) requires to open the Integration Services project

  1. *.dtproj
  2. *.dtproj.user
  3. *.dtsx
  4. *.database

[bg_collapse view=”button-green” color=”#FFF” icon=”arrow” expand_text=”Show Answer” collapse_text=”Hide Answer” ]Answer : *.database[/bg_collapse]
 
34
‘Annotation’ is used to

  1. Connect one control flow to another
  2. Connect one data flow task to another
  3. Make Comment in package designer Pane
  4. Make Comment inside the control flow/data flow task

[bg_collapse view=”button-green” color=”#FFF” icon=”arrow” expand_text=”Show Answer” collapse_text=”Hide Answer” ]Answer : Make Comment in package designer Pane[/bg_collapse]
 
35
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

[bg_collapse view=”button-green” color=”#FFF” icon=”arrow” expand_text=”Show Answer” collapse_text=”Hide Answer” ]Answer : Need to rewrite ActiveX Script functionality[/bg_collapse]



 

Related Articles

Advanced Manufacturing Process Quiz

Web analytics, Adobe Analytics, Google Analytics Quiz

SailPoint Quiz