SSIS Beginner Quiz

 



1) 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

 
 
2) 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

 
 
3) Which is not a standard Data Flow Transformation Task

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

Show Answer

 
 
4) Find out the Control Flow task

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

Show Answer

 
 
5) Find out the data flow task from the below component

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

Show Answer

 
 
6) Customer is having a SQL Server Analysis Services database and it is required to connect to Analysis Services Processing cube,  which connection manager type supports connecting to Analysis Services cube

1. MSMQ
2. SMOServer
3. ADO
4. MSOLAP100

Show Answer

 
 
7) What is the supported authentication by the FTP connection manager?

1. Anonymous authentication
2. Basic authentication
3. Windows Authentication
4. Both Anonymous and Basic authentication

Show Answer

 
 
8) Suppose you want to obtain the highest temperature of the day from a Web service method and assign to a variable and to connect which the connection manager is used to fetch the data

1. FTP
2. HTTP
3. WMI
4. MSMQ

Show Answer

 
 
9) The Cache connection manager reads data from the Cache transform or from a cache file or can save the data to a cache file. What is the file extension of the cache file

1. *.che
2. *.cah
3. *.cha
4. *.caw

Show Answer

 
 
10) Which is NOT a standard Built-In Connection Manager

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

Show Answer

 
11) 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

 
 
12) Which container groups and manage projects.

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

Show Answer

 
 
13) How to iterate a Recordset in Control Flow

1. Use Foreach loop
2. Use For loop
3. Use a Script Task
4. Use RecordSet source

Show Answer

 
 
14) Groups tasks and containers into control flows that are subsets of the package control flow

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

Show Answer

 
 
15) There is a sales Data Mart and Analysis Services cube is built and every data new fact data is coming and require to process the current day data in the Analysis Services cube, which is the right Destination type to process the partition data

1. Data Mining Model Training Destination
2. Dimension Processing Destination
3. SQL Server Destination
4. Partition Processing Destination

Show Answer

 
 
16) Data is sourced from SQL Server DB and the data required to be extracted into flat file to share with other systems to consume the data. Which is the right target destination type to choose?

1. Excel Destination
2. Raw File Destination
3. Flat File Destination
4. OLE DB Destination

Show Answer

 
 
17) There is a requirement to write raw data to a file and which is the correct destination type to choose

1. Flat File
2. Raw File
3. OLE DB
4. Excel

Show Answer

 
 
18) Integration Services supports log entries on predefined events and provides custom log entries for many Integration Services objects. During the execution of package and which scenario the OnInformation Event is raised and  writes the log entries.

1. When an error occurs
2. During the validation and execution of an executable to report information
3. When a warning occurs
4. When measurable progress is made by the executable.

Show Answer

 
 
19) The requirement is to raise an event whenever a variable value in the executable packages is changed, during the run-time which event handler option raises this event?

1. OnExecStatusChanged
2. OnVariableValueChanged with RaiseChangeEvent property set to False
3. OnVariableValueChanged and RaiseChangeEvent property value doesn’t impact
4. OnVariableValueChanged with RaiseChangeEvent property set to True

Show Answer

 
 
20) In a packages there are multiple levels Event Handlers are defined

1. a) Execute SQL Task
2. b) For Loop Container
3. c) Package level

and whenever there is an error occurs the package should fail.  There  is an error occurs when executing a SQL statement, which

1. Execute SQL Task raises an event
2. For Loop Container raises an event
3. Package event handler runs
4. Event handler are raised in each of the stages

Show Answer

 
21) Integration Services provides various Event Handlers and  this event is raised by an executable to determine whether it should stop running.

1. OnVariableValueChanged
2. OnQueryCancel
3. OnExecStatusChanged
4. OnPostExecute

Show Answer

 
 
22) Which statement is true with respect to truncation of a string literal
Option A) At Design time, generates a warning if it occurs in the pipeline
Option B) Generates an error if it occurs in the runtime
Option C) No Error generated at Runtime
 

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

Show Answer

 
 
23) What is the default option in the data flow task sources, transformations and destinations to handle errors and truncations

1. Ignore Failure
2. Redirect Row
3. Fail Component
4. No Defaults

Show Answer

 
 
24) 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

 
 
25) Which is not a legal cast operation when converting DT_DECIMAL data type

1. DT_DATE
2. DT_R4
3. DT_I4
4. DT_NUMERIC

Show Answer

 
 
26) You need to remove white-space characters such as the tab or line feed characters, which is right option to remove such white-spaces

1. TRIM
2. SUBSTRING
3. FINDSTRING
4. Script Component

Show Answer

 
 
27) Find out the component which is not a data flow source

1. OLEDB Source
2. Flat File Source
3. XML Source
4. ODBC Source

Show Answer

 
 
28) Consume data from a .NET Framework data provider, which is the built-in source type supports

1. OLE DB
2. Raw File
3. ADO
4. Script Component

Show Answer

 
 
29) Want to connect to Oracle Data Source and which is the Source Type used

1. OLEDB Source
2. ADO NET Source
3. Oracle Source
4. SQL Source

Show Answer

 
 
30) 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

 
31) Run a package that deletes files when the available memory on a server drops lower than a specified percentage, Which task can be used to perform the above step?

1. WMI Event Watcher Task
2. WMI Data Reader Task
3. File System Task
4. Web Service Task

Show Answer

 
 
32) With the Send Mail Task which is NOT a supported feature or correct statement with sending mails

1. Multiple attachments cannot be sent
2. If an attachment file does not exist when the package runs, an error occurs
3. The SMTP connection manager supports only anonymous authentication and Windows Authentication
4. The To, Cc, and Bcc lines are limited to 256 characters each in accordance with Internet standards

Show Answer

 
 
33) What is WMI stands for?

1. Windows Management Instrumentation
2. Windows Management Information
3. Windows Memory Information
4. Windows Memory Instrumentation

Show Answer

 
 
34) There are multiple packages created and need to create one master package and have to call the child packages in a particular sequence. And schedule only the master package. How the child packages are invoked from the master package.

1. Execute Process task
2. File System Task
3. XML Task
4. Execute Package task

Show Answer

 
 
35) 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

 
 
36) Which of the following tasks provides code to perform functions that are not available in the built-in tasks and transformation that SQL Server Integration Services provides.

1. Transfer Database task
2. Send Mail Task
3. Script task
4. FTP Task

Show Answer

 
 
37) Work Flow tasks that communicate with other processes to run packages, run programs or batch files, send and receive messages between packages, send e-mail messages, read Windows Management Instrumentation, Which is not a belong to Work Flow Task

1. Execute Process task
2. Send Mail Task
3. Transfer Jobs Task
4. WMI Data Reader Task

Show Answer

 
 
38) 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

 
 
39) 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

 
 
40) 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

 
41) 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

 
 
42) Which is not correct statement with the Row Count Transformation?

1. The Row Count transformation counts rows as they pass through a data flow and stores the final count in a variable
2. The variable must be in the scope of the Data Flow task to which the data flow with the Row Count transformation belongs
3. It does not support an error output
4. This transformation has one input and two outputs

Show Answer

 
 
43) Duplicate rows in the Data Flow Task can be removed using which Component

1. Sort
2. Lookup
3. Union All
4. Aggregate

Show Answer

 
 
44) Choose the statement which is not applicable or the statement is wrong when using Character Mapping Transformation

1. Operates only on column data with a string data type
2. Includes or supports Error Output
3. In Place data conversion is not supported
4. Can perform operations to convert a column to uppercase or lowercase

Show Answer

 
 
45) 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

 
 
46) _________ 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

 
 
47) To monitor which user login account is used and capture the details in the data flow in a package, choose the correct option which support this task

1. Export Column
2. Audit
3. Copy Column
4. Import Column

Show Answer

 
 
48) Which is the only configuration option available with system variables

1. Specify a namespace for the variable
2. Raise an event when they change value
3. Indicate whether the variable is read-only or read/write
4. Use the evaluation result of an expression to set the variable value

Show Answer

 
 
49) Integration Services supports two types of variables: user-defined variables and system variables, Choose the correct option

1. Only user-defined is case sensitive.
2. Both user-defined and system variables are NOT case sensitive.
3. Both user-defined and system variables are case sensitive.
4. Only system variable is case sensitive.

Show Answer

 
 
50) Which of the following is not a System variable

1. System::PackageID
2. System::UserName
3. System::CreatorName
4. System::ProductID

Show Answer

 
51) Integration Supported Variable types:
a) User Defined Variable
b) System Variable
c) User Defined System Variables
 

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

Show Answer

 
 
52) The execution result of the Precedence Executable is specified Completion, which is the correct statement and what happens with the constrained executable run

1. Requires the Precedence executable must complete successfully for the constrained executable to run
2. Requires the Precedence executable fail for the constrained executable to run
3. Requires only the precedence executable has completed, without regard to outcome
4. Success or failure outcome is important for the constrained executable to run

Show Answer

 
 
53) ____________ sequentially link tasks in a package

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

Show Answer

 
 
54)
When specify the execution result for precedence constraint, which is not the right option to specify the execution result

1. Success
2. Failure
3. Ignore
4. Completion

Show Answer

 
 
55) 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

 
 
56) ‘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

 
 
57) You are deploying a SQL Server Integration Services package. You have saved the package to a file system.
What is the file extension of the SQL Server Integration Services package that has been saved?

1. .ssis
2. .dtx
3. .dts
4. .dtsx

Show Answer

 
 
58) SQL Services Integration Services consists of different components and which is not part of Data Flow Component

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

Show Answer

 
 
59) Typical usage scenarios of Integration Services, Choose that options which is not the job of Integration Services

1. Merging Data from Heterogeneous Data Stores
2. Build Reports from different Data Sources
3. Cleaning and Standardizing Data
4. Populating Data Warehouses and Data Marts

Show Answer

 
 
60) Which is tool used to develop integration services packages with SQL Server Integration Services 2012 & above versions

1. SSDT
2. BIDS
3. SSMS
4. DTS

Show Answer