SQL Server Integration Services | SSIS Quiz

Posted on: | By: – Comments Off

SSIS Intermediate Quiz

In this SIS Quiz we are going to cover topics such as integration services, integration service sql server, sql server integration services, microsoft ssis, ssis microsoft, sql ssis, sql server ssis, ssis sql server, ssis in sql, ssis in sql server, what is ssis, sql database integration, what is integration services and ssis services.

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

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

4) From which version Project Deployment Model is supported by SQL Server Integration Services

  1. SQL Server 2005 & above
  2. SQL Server 2008 & above
  3. SQL Server 2008 R2 & above
  4. SQL Server 2012 & above
Answer : D

5) Integration Services provides three options for configuring transactions: NotSupported, Supported, and Required. Choosing the transaction option Required indicates

  1. That the container starts a transaction, unless one is already started by its parent container
  2. That the container does not start a transaction, but joins any transaction started by its parent container
  3. That the container does not start a transaction or join an existing transaction
  4. That the container starts a transaction irrespective of whether the parent container started the transaction or not
Answer : A

6) Need to configure a package to use a single transaction. And in the control flow there is a container task which include task to execute a set SQL Scripts with Execute SQL Task. Choose the correct option to set the single transaction

  1. At package control flow set the TransactionOption property to Required And at the container\task set the TransactionOption property to Required
  2. At package control flow set the TransactionOption property to Required And at the container\task set the TransactionOption property to Supported
  3. At package control flow set the TransactionOption property to Supported And at the container\task set the TransactionOption property to NotRequired
  4. At package control flow set the TransactionOption property to Supported And at the container\task set the TransactionOption property to Required
Answer : B

7) Transactions in packages used for the following purposes, choose the right option?
Option A) Ensure consistent updates on multiple database servers
Option B) Guarantee updates in an asynchronous environment
Option C) Carry out multiple transactions under the control of a single package

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

8) When a package performs a bulk operation, such as inserting data, data errors may occur. When errors occur, the data can contain both rows without errors as well as rows with errors. How to handle the rows with errors separately from those without errors?

  1. Merge Transformation
  2. Script Component
  3. Lookup Transformation
  4. Row Sampling Transformation
Answer : B

9) A package has a Sequence container that holds two Foreach Loop containers, and each container include two Execute SQL tasks. The Sequence container supports transactions, the Foreach Loop containers do not, and the Execute SQL tasks do. Sequence task was aborted and given the scenario find the incorrect statement?

  1. Execute SQL Tasks are rolled back
  2. All are unrelated transactions in the package
  3. Execute SQL Tasks are not rolled back
  4. Execute SQL Tasks start its own transaction
Answer : C

10) Which is not a supported transaction option provided by Integration Services.

  1. NotSupported
  2. Supported
  3. Required
  4. RequiresNew

Answer : D



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

  1. SSISDB catalog
  2. sysssislog
  3. sysssispackages
  4. sysssispackagefolders
Answer : B

12) 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
Answer : C

13) Want to log additional information with the Bulk Insert Task and which are the Custom log entries supported by the Bulk Insert task
a) DTSBulkInsertTaskBegin
b) DTSBulkInsertTaskEnd
c) DTSBulkInsertTaskInfos
d) DTSBulkInsertTaskError

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

14) Want to log additional information with the Send Mail Task and which are the Custom log entries supported by the Send Mail task
a) SendMailTaskBegin
b) SendMailTaskEnd
c) SendMailTaskInfo
d) SendMailTaskError

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

15) Applies the output from the Diff operation, called a Diffgram document, to an XML document, to create a new parent document that includes content from the Diffgram document. Which Operations supports the scenario to work with XML data of XML Tasks

  1. Diff
  2. Merge
  3. Path
  4. Xpath
Answer : C

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
Answer : C

17)
Transfer Database task connects to the source and destination servers using which connection manager?

  1. OLEDB
  2. ODBC
  3. SMOServer
  4. MSOLAP100
Answer : C

18) Which of the operation is not supported by the File System Task

  1. Create directory
  2. Create File
  3. Copy directory
  4. Copy File
Answer : B

19) Which of the following tasks used to fail the task when duplicate jobs exist?

  1. Maintenance Task
  2. Transfer Jobs Task
  3. Execute Process Task
  4. Transfer Database Task
Answer : B

20) The Data Profiling Task can analyze both individual columns and analyze multiple columns or relationships between columns and tables, Choose the option which is not part Individual column profiling

  1. Candidate Key Profile
  2. Column Length Distribution Profile
  3. Column Null Ratio Profile
  4. Column Statistics Profile

Answer : A



21) With the Data Profiling Task Profiles, Which profile helps to Report all the distinct values in the selected column and the percentage of rows in the table that each value represents. Can also report values that represent more than a specified percentage of rows in the table

  1. Column Pattern Profile
  2. Column Length Distribution Profile
  3. Column Value Distribution Profile
  4. Column Null Ratio Profile
Answer : C

22) Depending on the connection type that the Execute SQL task uses, the syntax of the SQL command uses different parameter markers and Parameter Names. For example, the ADO connection manager type requires that the SQL command uses a parameter marker in the format of __________and parameter name in the format of __________

  1. Parameter Marker ? Parameter Name Param1, Param2, …
  2. Parameter Marker @<parameter name> Parameter Name @<parameter name>
  3. Parameter Marker ? Parameter Name 1, 2, 3, …
  4. Parameter Marker ? Parameter Name 0,1, 2, 3, …
Answer : A

23) The Transfer Error Messages task can be configured to handle existing error messages in the following ways

  1. Overwrite existing error messages
  2. Fail the task when duplicate messages exist
  3. Skip duplicate error messages
  4. All of the above.
Answer : D

24) 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
Answer : A

25) 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.
Answer : D

26) ____________ 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
Answer : B

27) Need to get the highest temperature of the day from a Web service method, and then use that value to update a variable that is used in an expression that sets a column value. Choose correct option to get the highest temperature

  1. WMI Task
  2. WSDL Task
  3. Web Service Task
  4. HTTP Connection Task
Answer : C

28) Overwrite existing stored procedures can be achieved by

  1. Transfer Database Task
  2. TransferMaster Stored procedure task
  3. Transfer Jobs Task
  4. Transfer SQL Server Objects Tasks
Answer : B

29) FTP task supports file transfer and to specify the path to transfer a text file and which is not valid with specifying the path

  1. C:\Sharedpath\*.txt
  2. C:\Sharedpath\*\*.txt
  3. C:\Sharedpath\transfer.txt
  4. C:\Sharedpath\transfer*.txt
Answer : B

30) Which is not a true statement with respect to features supported by Bulk Insert Task

  1. Only members of the sysadmin fixed server role can run a package that contains a Bulk Insert task
  2. The Bulk Insert task can transfer data only from a text file
  3. The destination must be a table or view in a SQL Server database
  4. With every time loading data Bulk Insert Task replaces existing data

Answer : D



31) Depending on the connection type that the Execute SQL task uses, the syntax of the SQL command uses different parameter markers. For example, the ADO.NET connection manager type requires that the SQL command uses a parameter marker in the format _______

  1. Parameter Marker ?
  2. Parameter Marker @<parameter name>
  3. Parameter Marker #
  4. Parameter Marker @@
Answer : B

32) 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 functionality

  1. Conditional Split Transformation
  2. Aggregate Transformation
  3. Lookup Transformation
  4. Multicast Transformation
Answer : D

33) 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 AB&C
Answer : A

34) Which is not a supported feature or standard behavior of Union ALL Transformation

  1. This transformation has multiple inputs and one output
  2. Does not support an error output
  3. Output rows are sorted
  4. Input not mapped to output are set to null values in the output columns
Answer : C

35) Conditional Split Transformation can be configured for the following except?

  1. Specify the Order in which the conditions are evaluated and Order is significant
  2. Specify the default output for the transformation
  3. Route data rows to single output
  4. Each input row can be sent to only one output
Answer : C

36) 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
Answer : C

37) Which statement is not correct with Cache Transform?

  1. The Cache Transform writes only unique rows to the Cache connection manager
  2. In a single package, only one Cache Transform can write data to the same Cache connection manager
  3. Each input column must be mapped to a destination column
  4. Input column and destination column not necessary to match
Answer : D

38) If you have to use the script to do the same work for each row of data in a set, you should use

  1. Script component
  2. Script task
  3. Execute Process Task
  4. Execute Package Task
Answer : A

39) The Lookup transformation has the following outputs

  1. a) Match Output
  2. b) No Match Output
  3. c) Error output

Choose the correct options

  1. Option A &B
  2. Option A & C
  3. Option B & C
  4. Option AB&C
Answer : D

40) Which is not a supported cache mode of Lookup Transformation

  1. No Cache
  2. Partial Cache
  3. Full Cache
  4. Standard Cache

Answer : D



41) You require the same data in the transformation pipeline one to aggregate the data and another copy is extended by having lookup values and derived. Which Transformation helps to perform this?

  1. Copy Column Transformation
  2. Conditional Split Transformation.
  3. Multicast Transformation
  4. Lookup Transformation
Answer : C

42) 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
Answer : D

43) The logins to be transferred may already exist on the destination. The Transfer Logins task can be configured to handle existing logins in the following ways choose all correct option\options

  1. Overwrite existing logins.
  2. Fail the task when duplicate logins exist
  3. Skip duplicate logins
  4. All of the above
Answer : D

44) 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
Answer : D

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

  1. OLEDB Source
  2. Flat File Source
  3. XML Source
  4. ODBC Source
Answer : D

46) 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
Answer : C

47) Merge transformation in a data flow, you can perform
a) Merge data from two data sources, such as tables and files.
b)Create complex datasets by nesting Merge transformations.
c) Remerge rows after correcting errors in the data.
Choose all that apply

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

48) 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
Answer : B

49) 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
Answer : D

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

Answer : C



51) To troubleshoot Control Flow which is not a supported feature\tool in packages during package development?

  1. Set breakpoints on containers
  2. Use the debugging windows
  3. Review the information on the Progress tab
  4. Use data viewers
Answer : D

52) To troubleshoot Data Flow which is not a supported feature\tool in packages during package development?

  1. Set breakpoints on containers
  2. Capture the count of rows processed.
  3. Review the information on the Progress tab
  4. Use data viewers
Answer : A

53) In the development environment When you run a package, SSIS Designer depicts execution progress by displaying each task or container using a color that indicates execution status, the execution color status is showing Gray and what Gray depicts on the execution status

  1. Waiting to run
  2. Running
  3. Ran successfully
  4. Ran successfully
Answer : A

54)
In the development environment you need to enable a break condition on the For Loop container that sets a breakpoint and to suspend execution at the start of each iteration of the loop, what is the right option to specify for the Hit count type.

  1. Always
  2. Hit count equals
  3. Hit count greater than or equal to
  4. Hit count multiple
Answer : A

55) When you run a package in the dev environment, SSIS Designer depicts progress on the design surface of the Data Flow tab by displaying each data flow component in a color that indicates status, What does Yellow color indicates?

  1. Waiting to be called by the data flow engine
  2. Performing a transformation, extracting data, or loading data
  3. Ran successfully
  4. Ran with errors.
Answer : B

56) When you want to convert from a string to a date or time data type, the string must use the string format that corresponds to the appropriate date or time data type. To successfully convert some date strings to the DT_DBDATE data type, these date strings must be in the format

  1. yyyy-mm-dd
  2. yyyy-mm-dd hh:mm:ss[.fffffff] [{+|-} hh:mm]
  3. hh:mm:ss[.fffffff]
  4. yyyy-mm-dd hh:mm:ss[.fff]
Answer : C

57) Integration Services provides parsing support to convert data and which are ones supported
a) Fast Parse
b) Slow Parse
c) Standard Parse

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

58) There are options available to change the data type of a column and choose the correct option\s
a) Use an expression to implicitly convert data types
b) Use the cast operator to convert data types
c) Use the Data Conversion transformation to cast the data type
d) Use the Derived Column transformation to create a copy of a column

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

59) Converting from DT_FILETIME to DT_DBDATE results with

  1. Removes the date value.
  2. Removes the time value
  3. No Change
  4. Removes the fractional second value
Answer : B

60) To successfully convert some date strings to the DT_DBTIMESTAMP data type the date strings must be in the format of ________

  1. hh:mm:ss
  2. yyyy-mm-dd hh:mm:ss[.fff]
  3. yyyy-mm-dd
  4. yyyy-mm-dd hh:mm:ss[.fffffff] [{+|-} hh:mm]
Answer : B

Related Articles

Advanced Manufacturing Process Quiz

Web analytics, Adobe Analytics, Google Analytics Quiz

SailPoint Quiz