Microsoft 70-463 Exam Practice Test Instant Access

Question 1

You are developing a SQL Server Integration Services (SSIS) package.

The package is stored as the myPackage.dtsx file in the root directory of the C drive of the SSIS server.

You need to run the package from the command prompt.

Which command should you use?

Adtexec /sql myPackage

Bdtexec /d 'C:\File System\myPackage.dtsx'

Cdtexec /f 'C:\myPackage.dtsx'

Ddtexec /com 'myPackage.dtsx'

Answer : C

Question 2

Reporting from a Star schema is simpler than reporting from a normalized online transactional processing

(OLTP) schema.

What are the reasons for wanting simpler reporting? (Choose all that apply.)

AA Star schema typically has fewer tables than a normalized schema. Therefore, queries are simpler because they require fewer joins.

BA Star schema has better support for numeric data types than a normalized relational schema; therefore, it is easier to create aggregates.

CThere are specific Transact-SQL expressions that deal with Star schemas.

DA Star schema is standardized and narrative; you can find the information you need for a report quickly.

Answer : A, D

References:

http://www.vertabelo.com/blog/technical-articles/data-warehouse-modeling-star-schema-vs-snowflake-schema

Question 3

You are designing a SQL Server Integration Services (SSIS) data flow to load sales transactions from a source system into a data warehouse hosted on Windows Azure SQL Database. One of the columns in the data source is named ProductCode.

Some of the data to be loaded will reference products that need special processing logic in the data flow.

You need to enable separate processing streams for a subset of rows based on the source product code.

Which Data Flow transformation should you use?

AMulticast

BConditional Split

CScript Task

DData Conversion

Answer : B

We use Conditional Split to split the source data into separate processing streams.

A Script Component (Script Component is the answer to another version of this question) could be used but this is not the same as a Script Task.

Question 4

You are designing a SQL Server Integration Services (SSIS) solution. The solution will contain an SSIS project that includes several SSIS packages. Each SSIS package will define the same connection managers and variables.

You have the following requirements:

* The deployment model must support changing the content of connection strings by using parameters at execution time.

* The deployment model must automatically log events to the SSISDB database.

* Maximize performance at execution time.

You need to design a solution that meets the requirements. What should you do? (More than one answer choice may achieve the goal. Select the BEST answer.)

AUse a project deployment model. Modify connection manager properties to use project parameters.

BUse a package deployment model. Save each SSIS package to a file share that can be accessed from all environments.

CUse a package deployment model. Configure connections in an XML configuration file referenced by an environment variable that corresponds to the SQL Server environment of each SSIS package.

DUse a project deployment model. Modify connection manager properties to use package parameters.

Answer : A

Question 5

You are developing a SQL Server Integration Services (SSIS) package that imports data into a data warehouse. You add an Execute SQL task to the control flow.

The task must execute a simple INSERT statement. The task has the following requirements:

* The INSERT statement must use the value of a string package variable. The variable name is StringVar.

* The Execute SQL task must use an OLE DB Connection Manager.

In the Parameter Mapping tab of the Execute SQL task, StringVar has been added as the only parameter.

You must configure the SQLStatement property of the Execute SQL task.

Which SQL statement should you use?

AINSERT INTO dbo.Table (variablevalue) VALUES ($StringVar)

BINSERT INTO dbo.Table (variablevalue) VALUES (0)

CINSERT INTO dbo.Table (variablevalue) VALUES (@0)

DINSERT INTO dbo.Table (variablevalue) VALUES (?)

Answer : D

References:

http://msdn.microsoft.com/en-us/library/ms141003.aspx

http://msdn.microsoft.com/en-us/library/ms140355.aspx

http://msdn.microsoft.com/en-us/library/cc280502.aspx

Question 6

You are implementing a new SQL Server Integration Services (SSIS) 2012 package that loads data from various flat files and a Windows Azure SQL Database database.

Daily transactions must be loaded into a staging database. All the SSIS tasks will use the CurrentDate variable as the transaction date.

You need to set the CurrentDate variable to the current execution date when the package starts. You need to achieve this goal by using the least amount of development effort.

What should you use to set the variable?

AAn Expression task

BA Script component

CA Script task

DAn Execute SQL task

Answer : A

Ref: http://msdn.microsoft.com/en-us/library/ms141214.aspx

http://blog.sqltechie.com/2012/11/expression-task-in-ssis-2012.html

Question 7

A SQL Server Integration Services (SSIS) package was deployed two weeks ago with the Project Deployment Model.

Sometimes the package is started as part of a multistep SQL job. At other times, the package is started manually by a database administrator by using the Object Explorer in SQL Server Management Studio.

You need to identify the authenticated user responsible for starting the package each time it executes.

How can you find this information?

AIn the SSISDB.[catalog], query the .[executions] view.

BIn the SSISDB.[catalog] , query the [event_messages] view.

CIn SQL Server Management Studio, view the SQL Agent Job History.

DIn SQL Server Management Studio, view the SQL Agent Error Log.

EIn SQL Server Management Studio, view the SQL Server Log.

Answer : A

Microsoft Implementing a Data Warehouse with Microsoft SQL Server 2012/2014 70-463 Exam Practice Test