SSIS Beginner Quiz

1
Which is NOT a standard Built-In Connection Manager

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

Show Answer

 
2
Which is not a standard Data Flow Transformation Task

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

Show Answer

 
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

Show Answer

 
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

Show Answer

 
5
Find out the Control Flow task

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

Show Answer

 
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

Show Answer

 
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

Show Answer

 
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

Show Answer

 
9
Which container groups and manage projects.

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

Show Answer

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

Show Answer

 
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

Show Answer

 
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

Show Answer

 
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

Show Answer

 
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

Show Answer

 
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

Show Answer

 
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

Show Answer

 
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.

Show Answer

 
18
____________ sequentially link tasks in a package

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

Show Answer

 
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

Show Answer

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

Show Answer

 
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.

Show Answer

 
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

Show Answer

 
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

Show Answer

 
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

Show Answer

 
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

Show Answer

 
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

Show Answer

 
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

Show Answer

 
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.

Show Answer

 
29
Where does the SQL Server Log Provide Writes the log entries to?

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

Show Answer

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

Show Answer

 
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

Show Answer

 
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

Show Answer

 
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

Show Answer

 
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

Show Answer

 
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

Show Answer