Question 1

What is the drawback to using a linked server for integrating an external data source?&#10;A)it enables a data source to be queried directly from within SQL Server&#10;B)it enables a client application to efficiently perform bulk copy operations&#10;C)it doesn't work well for dynamic data sources&#10;D)you must configure it using the bcp utility

Accepted Answer

Linked servers work best for static data sources that do not frequently change, as they can become unstable if the external data source is dynamic. Additionally, configuring a linked server does not require the use of the bcp utility, as it can be done through SQL Server Management Studio or T-SQL scripts. Options A and B are benefits of using a linked server rather than drawbacks.

Question 2

Which basic data integration tool provides step-by-step instructions but has a limited range of data transformation capabilities?&#10;A)SQL Server Import and Export Wizard&#10;B)Integration Services Package&#10;C)SQL Server Analysis Services&#10;D)SQL Server Data Tools

Accepted Answer

SQL Server Import and Export Wizard provides step-by-step instructions for basic data integration tasks, but it has a limited range of data transformation capabilities compared to other tools like Integration Services and Data Tools.

Question 3

Which of the following is NOT a method that can be used to execute an Integration Services package?&#10;A)dtexec.exe&#10;B)dtexecui.exe&#10;C)SQL Server Agent job&#10;D)Windows task scheduler

Accepted Answer

Windows Task Scheduler is not a direct method for executing Integration Services packages, though it can be used indirectly by scheduling a batch file that calls dtexec.exe.

Question 4

A package configuration file can be generated from with SQL Server Data Tools for each environment a package will run in.

Accepted Answer

Yes, a package configuration file can be generated from within SQL Server Data Tools for each environment a package will run in. The configuration file specifies values for properties and variables needed by a package at runtime. By creating a configuration file for each environment, you can ensure that the package behaves consistently across different environments.

Question 5

What type of component should you use if you need to convert numeric data to string data as the data flows between the source and destination?&#10;A)script&#10;B)file system task&#10;C)data flow&#10;D)transformation

Accepted Answer

The transformation component in SSIS is used for data conversion and transformation. The "Data Conversion" transformation can be used to convert data types, including converting numeric data to string data.

Question 6

ODBC is an API that is designed to facilitate the interactions between a client application and a database management system, but it is platform-specific.

Accepted Answer

ODBC (Open Database Connectivity) is designed to be a platform-independent API, allowing client applications to communicate with various database management systems across different operating systems.

Question 7

Which of the following may be found in a database table that performs a secondary set of operations when data is inserted, deleted, or updated?&#10;A)procedure&#10;B)trigger&#10;C)log&#10;D)transaction

Accepted Answer

A trigger is a special kind of stored procedure that is executed automatically in response to certain events or changes in a database table, including data being inserted, deleted, or updated. The trigger can perform a secondary set of operations or validations based on the changes made to the table. Therefore, choice B (trigger) is the best answer for this question.

Question 8

Configuring a linked server which enables a data source to be queried directly from within SQL Server works well for static data sources.

Accepted Answer

Configuring a linked server allows for querying data sources directly from within SQL Server. While this process may require some configuration, once set up it can be a useful tool for querying static data sources.

Question 9

Which type of SSIS package task permits the package to interact with other applications?&#10;A)data flow&#10;B)scripting&#10;C)workflow&#10;D)maintenance

Accepted Answer

The answer of Which type of SSIS package task permits...

Question 10

The BULK INSERT statement inserts records from an external data file source into a SQL Server table.

Accepted Answer

The BULK INSERT statement is used to insert large amounts of data from an external data file into a SQL Server table. It is a very efficient way to load data into a database and is often used for data migration or data warehousing purposes.

Question 11

Which of the following is true about packages created with SQL Server Data Tools?&#10;A)you need to manually create a folder named &#34;SSIS packages&#34;&#10;B)the default package name is default.dtsx&#10;C)projects can contain multiple Integration Services packages&#10;D)the user interface is a command prompt

Accepted Answer

The answer of Which of the following is true about...

Question 12

Aside from extracting and loading data, the process of data integration involves which of the following?&#10;A)deleting data&#10;B)transforming data&#10;C)summarizing data&#10;D)selecting data

Accepted Answer

The answer of Aside from extracting and loading data, the...

Question 13

Which type of task should you add to an SSIS package if you need to perform operations on schema objects?&#10;A)SQL Server&#10;B)workflow&#10;C)maintenance&#10;D)data preparation

Accepted Answer

The answer of Which type of task should you add...

Question 14

What kind of task should be added to an SSIS package if you want to download a data file from an FTP site?&#10;A)data flow&#10;B)data preparation&#10;C)workflow&#10;D)SQL Server

Accepted Answer

The answer of What kind of task should be added...

Question 15

Which of the following can be used for bidirectional data transfers and is a stand-alone application that is run from a command prompt?&#10;A)BULK INSERT SQL&#10;B)bcp utility&#10;C)SSIS&#10;D)ETL

Accepted Answer

The answer of Which of the following can be used...

Question 16

Which of the following is NOT a method to leverage the bulk copy interface?&#10;A)run the bcp utility&#10;B)create a SQL Server Agent job&#10;C)use the BULK INSERT function&#10;D)interact with the API directly

Accepted Answer

The answer of Which of the following is NOT a...

Question 17

Which of the following is a legacy data integration tool with a graphical designer that had a limited range of functionality?&#10;A)DTS&#10;B)SSIS&#10;C)ETL&#10;D)FTP

Accepted Answer

The answer of Which of the following is a legacy...

Question 18

Which of the following are the basic building blocks of an SSIS package?&#10;A)processes&#10;B)flows&#10;C)functions&#10;D)tasks

Accepted Answer

The answer of Which of the following are the basic...

Question 19

What should be used if you want an email alert to be sent to you if an error occurs in the SSIS package?&#10;A)user variable&#10;B)event handler&#10;C)connection manager&#10;D)maintenance task

Accepted Answer

The answer of What should be used if you want...

Question 20

The bcp utility can be executed to insert records from an external data file source into a SQL Server table.

Accepted Answer

The answer of The bcp utility can be executed to...

Question 21

How are data sources defined in a data flow task?

Accepted Answer

The answer of How are data sources defined in a...

Question 22

What is a workflow task? Provide an example.

Accepted Answer

The answer of What is a workflow task? Provide an...

Question 23

What are SSIS packages, how are they stored, and what are their basic building blocks?

Accepted Answer

The answer of What are SSIS packages, how are they...

Question 24

Which of the following is the correct syntax for using the BULK INSERT statement?&#10;A)BULK INSERT . . WITH ' ' FROM ( )&#10;B)BULK INSERT FROM . . WITH ' ' OPTIONS&#10;C)BULK INSERT . . FROM ' ' WITH ( )&#10;D)BULK INSERT WITH . . FROM ' ' OPTIONS

Accepted Answer

The answer of Which of the following is the correct...

Question 25

What is control flow and how can it be altered in an SSIS package?

Accepted Answer

The answer of What is control flow and how can...

Question 26

What are two methods you can use to execute an Integration Services package?

Accepted Answer

The answer of What are two methods you can use...

Question 27

___________ tasks allow custom code to be executed to extend the functionality of the basic set of tasks.

Accepted Answer

The answer of ___________ tasks allow custom code to be...

Question 28

Why would you use a format file used with the bcp utility?

Accepted Answer

The answer of Why would you use a format file...

Question 29

An application programming ____________ defines the communication protocol between two software applications that need to interact with each other.

Accepted Answer

The answer of An application programming ____________ defines the communication...

Question 30

Match between columns&#10;                        Premises: tasks that perform operations on schema objects and data on a SQL server&#10;tasks that perform operations on schema objects and data on a SQL server&#10;tasks that perform operations on schema objects and data on a SQL server&#10;tasks that perform operations on schema objects and data on a SQL server&#10;tasks that perform operations on schema objects and data on a SQL server&#10;tasks that perform operations on schema objects and data on a SQL server&#10;tasks that perform operations on schema objects and data on a SQL server&#10;tasks that perform operations on schema objects and data on a SQL server&#10;tasks that perform operations on schema objects and data on a SQL server&#10;tasks that perform operations on schema objects and data on a SQL server&#10;a storage unit within an SSIS package that can hold both static values or whose values can be set at run time&#10;a storage unit within an SSIS package that can hold both static values or whose values can be set at run time&#10;a storage unit within an SSIS package that can hold both static values or whose values can be set at run time&#10;a storage unit within an SSIS package that can hold both static values or whose values can be set at run time&#10;a storage unit within an SSIS package that can hold both static values or whose values can be set at run time&#10;a storage unit within an SSIS package that can hold both static values or whose values can be set at run time&#10;a storage unit within an SSIS package that can hold both static values or whose values can be set at run time&#10;a storage unit within an SSIS package that can hold both static values or whose values can be set at run time&#10;a storage unit within an SSIS package that can hold both static values or whose values can be set at run time&#10;a storage unit within an SSIS package that can hold both static values or whose values can be set at run time&#10;a text file that contains data&#10;a text file that contains data&#10;a text file that contains data&#10;a text file that contains data&#10;a text file that contains data&#10;a text file that contains data&#10;a text file that contains data&#10;a text file that contains data&#10;a text file that contains data&#10;a text file that contains data&#10;tasks that move data between a source and a destination&#10;tasks that move data between a source and a destination&#10;tasks that move data between a source and a destination&#10;tasks that move data between a source and a destination&#10;tasks that move data between a source and a destination&#10;tasks that move data between a source and a destination&#10;tasks that move data between a source and a destination&#10;tasks that move data between a source and a destination&#10;tasks that move data between a source and a destination&#10;tasks that move data between a source and a destination&#10;the order in which each task in an SSIS package is executed&#10;the order in which each task in an SSIS package is executed&#10;the order in which each task in an SSIS package is executed&#10;the order in which each task in an SSIS package is executed&#10;the order in which each task in an SSIS package is executed&#10;the order in which each task in an SSIS package is executed&#10;the order in which each task in an SSIS package is executed&#10;the order in which each task in an SSIS package is executed&#10;the order in which each task in an SSIS package is executed&#10;the order in which each task in an SSIS package is executed&#10;tasks that perform essential database maintenance functions&#10;tasks that perform essential database maintenance functions&#10;tasks that perform essential database maintenance functions&#10;tasks that perform essential database maintenance functions&#10;tasks that perform essential database maintenance functions&#10;tasks that perform essential database maintenance functions&#10;tasks that perform essential database maintenance functions&#10;tasks that perform essential database maintenance functions&#10;tasks that perform essential database maintenance functions&#10;tasks that perform essential database maintenance functions&#10;tasks that permit an SSIS package to interact with other applications&#10;tasks that permit an SSIS package to interact with other applications&#10;tasks that permit an SSIS package to interact with other applications&#10;tasks that permit an SSIS package to interact with other applications&#10;tasks that permit an SSIS package to interact with other applications&#10;tasks that permit an SSIS package to interact with other applications&#10;tasks that permit an SSIS package to interact with other applications&#10;tasks that permit an SSIS package to interact with other applications&#10;tasks that permit an SSIS package to interact with other applications&#10;tasks that permit an SSIS package to interact with other applications&#10;a stand-alone application that is run from a command prompt and that can be used for bidirectional data transfer&#10;a stand-alone application that is run from a command prompt and that can be used for bidirectional data transfer&#10;a stand-alone application that is run from a command prompt and that can be used for bidirectional data transfer&#10;a stand-alone application that is run from a command prompt and that can be used for bidirectional data transfer&#10;a stand-alone application that is run from a command prompt and that can be used for bidirectional data transfer&#10;a stand-alone application that is run from a command prompt and that can be used for bidirectional data transfer&#10;a stand-alone application that is run from a command prompt and that can be used for bidirectional data transfer&#10;a stand-alone application that is run from a command prompt and that can be used for bidirectional data transfer&#10;a stand-alone application that is run from a command prompt and that can be used for bidirectional data transfer&#10;a stand-alone application that is run from a command prompt and that can be used for bidirectional data transfer&#10;an object within an SSIS package that stores the configuration information necessary to connect to an individual data source&#10;an object within an SSIS package that stores the configuration information necessary to connect to an individual data source&#10;an object within an SSIS package that stores the configuration information necessary to connect to an individual data source&#10;an object within an SSIS package that stores the configuration information necessary to connect to an individual data source&#10;an object within an SSIS package that stores the configuration information necessary to connect to an individual data source&#10;an object within an SSIS package that stores the configuration information necessary to connect to an individual data source&#10;an object within an SSIS package that stores the configuration information necessary to connect to an individual data source&#10;an object within an SSIS package that stores the configuration information necessary to connect to an individual data source&#10;an object within an SSIS package that stores the configuration information necessary to connect to an individual data source&#10;an object within an SSIS package that stores the configuration information necessary to connect to an individual data source&#10;a collection of one or more tasks grouped together as a series of logical steps&#10;a collection of one or more tasks grouped together as a series of logical steps&#10;a collection of one or more tasks grouped together as a series of logical steps&#10;a collection of one or more tasks grouped together as a series of logical steps&#10;a collection of one or more tasks grouped together as a series of logical steps&#10;a collection of one or more tasks grouped together as a series of logical steps&#10;a collection of one or more tasks grouped together as a series of logical steps&#10;a collection of one or more tasks grouped together as a series of logical steps&#10;a collection of one or more tasks grouped together as a series of logical steps&#10;a collection of one or more tasks grouped together as a series of logical steps

Accepted Answer

The Answer of control flow and SQL Server tasks and is...

Question 31

What should you use when running the bcp utility if you want to import only certain columns and run the command interactively?&#10;A)copy exclusion&#10;B)import filter&#10;C)schema view&#10;D)format file

Accepted Answer

The answer of What should you use when running the...

Question 32

SSIS is a component of SQL Server that can be used to ___________ many different types of external data sources.

Accepted Answer

The answer of SSIS is a component of SQL Server...

Question 33

Describe the SQL Server Import and Export Wizard. What are its advantages and limitations?

Accepted Answer

The answer of Describe the SQL Server Import and Export...

Question 34

An SSIS ___________ groups together a set of one or more tasks into a single object that can be executed using the SQL Server Integration Services service.

Accepted Answer

The answer of An SSIS ___________ groups together a set...

Question 35

How does SQL Server 2012 support ODBC for bulk copy operations?

Accepted Answer

The answer of How does SQL Server 2012 support ODBC...

Question 36

SQL Server Data Tools have a selection of predefined project ___________ that include the Integration Services Business Intelligence project for designing Integration Services packages.

Accepted Answer

The answer of SQL Server Data Tools have a selection...

Question 37

What factors should a database administrator consider when selecting the appropriate data integration tools for a given situation?

Accepted Answer

The answer of What factors should a database administrator consider...

Question 38

Describe SQL Server Data Tools. How can it be used to create SSIS packages?

Accepted Answer

The answer of Describe SQL Server Data Tools. How can...

Deck 9: Data Integration