Microsoft 70-463 Online Practice Questions and Exam Preparation

Microsoft 70-463 Online Questions & Answers

• Question 151:

  You administer a Microsoft SQL Server 2012 database. The database contains a table named Employee. Part of the Employee table is shown in the exhibit.

  

  

  Confidential information about the employees is stored in a separate table named EmployeeData. One record exists within EmployeeData for each record in the Employee table. You need to assign the appropriate constraints and table properties to ensure data integrity and visibility. On which column in the Employee table should you create a unique constraint?

  A. DateHired
  B. DepartmentID
  C. EmployeelD
  D. EmployeeNum
  E. FirstName
  F. JobTitle
  G. LastName
  H. MiddleName
  I. ReportsToID
  D. EmployeeNum

• Question 152:

  Note: This question is a part of a series of questions that use the same or similar answer choices. An answer choice may be correct for more than one question in the series. Each question is independent of the other questions in this series.

  Information and details provided in a question apply only to that question.

  You are implementing a SQL Server Integration Services (SSIS) package that loads data hosted in a Microsoft Azure SQL Database database into a data warehouse.

  The source system contains redundant or inconsistent data. When the package finds invalid data, the row containing the invalid data must be omitted but it must also be written to a text file for further analysis.

  You need to establish the best technique to log these invalid rows while keeping the amount of development effort to a minimum.

  What should you do?

  A. Add an OnError event handler to the SSIS project.
  B. Use an msi file to deploy the package on the server.
  C. Open a command prompt and run the gacutil command.
  D. Open a command prompt and run the dtutil /copy command.
  E. Open a command prompt and run the dtexec /rep /conn command.
  F. Open a command prompt and run the dtexec /dumperror /conn command.
  G. Run the package by using the dtexecui.exe utility and the SQL Log provider..
  H. Create a reusable custom logging component and use it in the SSIS project.
  I. Configure the SSIS solution to use the Project Deployment Model.
  J. Configure the output of a component in the package data flow to use a data tap.
  J. Configure the output of a component in the package data flow to use a data tap.

  ---

  References: http://technet.microsoft.com/en-us/library/hh230989.aspx http://www.rafael-salas.com/2012/01/ssis-2012-quick-peek-to-data-taps.html http://msdn.microsoft.com/en-us/library/ms162820.aspx http://msdn.microsoft.com/en-us/library/hh231187.aspx http://technet.microsoft.com/en-us/library/ms140223.aspx http://msdn.microsoft.com/en-us/library/jj655339.aspx

• Question 153:

  You work as a database developer at ABC.com. The ABC.com network consists of a single domain named ABC.com. ABC.com makes use of Microsoft SQL Server 2012 in their environment.

  You have been instructed to upgrade SQL Server 2008 Business Intelligence Development Studio (BIDS) package to SQL Server 2012. The package has custom scripts configured.

  Which of the following actions should you take?

  A. You should consider making use of dtexecui.exe from the command prompt.
  B. You should consider making use of gacutil.exe from the command prompt.
  C. You should consider making use of dtutil.exe from the command prompt.
  D. You should consider making use of SSISUpgrade.exe file from the command prompt.
  D. You should consider making use of SSISUpgrade.exe file from the command prompt.

• Question 154:

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

  You use a Data Profiling task to examine the data from a source system. You need to establish:

  

  The minimum and maximum dates for the datetime columns in the source data

  

  The minimum, maximum, and average values for numeric columns in the source data

  You need to use the appropriate profile type in the Data Profiling task.

  Which profile type should you use? (To answer, select the appropriate profile type in the answer area.)

  Hot Area:

  

  

• Question 155:

  You are deploying a new SQL Server Integration Services (SSIS) package to five servers.

  The package must meet the following requirements:

  

  .NET Common Language Runtime (CLR) integration in SQL Server must not be enabled.

  

  The Connection Managers used in the package must be configurable without editing and redeploying the package.

  

  The deployment procedure must be automated as much as possible.

  

  Performance must be maximized.

  You need to set up a deployment strategy that meets the requirements.

  What should you do?

  A. Add an OnError event handler to the SSIS project.
  B. Use an msi file to deploy the package on the server.
  C. Open a command prompt and run the gacutil command.
  D. Open a command prompt and run the dtutil /copy command.
  E. Open a command prompt and run the dtexec /rep /conn command.
  F. Open a command prompt and run the dtexec /dumperror /conn command.
  G. Open a command prompt and execute the package by using the SQL Log provider and running the dtexecui.exe utility.
  H. Create a reusable custom logging component and use it in the SSIS project.
  I. Configure the SSIS solution to use the Project Deployment Model.
  J. Configure the output of a component in the package data flow to use a data tap.
  D. Open a command prompt and run the dtutil /copy command.

• Question 156:

  You work as a senior database administrator at ABC.com. The ABC.com network consists of a single domain named ABC.com. ABC.com makes use of Microsoft SQL Server 2012 in their environment.

  It is reported that a SQL Server Integration Services (SSIS) package regularly stops working in production. The package is often started manually by an SSIS developer.

  You want to know who the authenticated user is.

  Which of the following actions should you take?

  A. You should consider making use of the catalog.packages SSISDB Database Transact-SQL statement.
  B. You should consider making use of the catalog.executions SSISDB Database Transact-SQL statement.
  C. You should consider making use of the catalog.environments SSISDB Database Transact-SQL statement.
  D. You should consider making use of the catalog.executables SSISDB Database Transact-SQL statement.
  B. You should consider making use of the catalog.executions SSISDB Database Transact-SQL statement.

• Question 157:

  You are developing a SQL Server Integration Services (SSIS) package to implement an incremental data load strategy.

  The package reads data from a source system. Depending on the value in a source column, the package redirects rows to one of five different data flow paths.

  You need to add a data flow transformation to support the package redirection.

  Which data flow transformation should you use?

  A. Conditional Split
  B. Pivot
  C. Multicast
  D. Lookup
  A. Conditional Split

• Question 158:

  You are reviewing the design of a student dimension table in an existing data warehouse hosted on Microsoft Azure SQL Database.

  The current dimension design does not allow the retention of historical changes to student attributes such as ParentOccupation.

  You need to redesign the dimension to enable the full historical reporting of changes to multiple student attributes including ParentOccupation.

  What should you do?

  A. Add CurrentValue and PreviousValue columns to the student dimension.
  B. Enable Snapshot Isolation on the data warehouse.
  C. Add an IsCurrent column to the student dimension.
  D. Add StartDate and EndDate columns to the student dimension.
  D. Add StartDate and EndDate columns to the student dimension.

  ---

  Adding a start and end date will give you this ability as when a record is inserted and given a start and end date, you'll have the ability to determine when they were active therefore giving you a retention of historical changes

• Question 159:

  Your company uses a proprietary encryption algorithm to secure sensitive data. A custom cryptographic assembly was developed in Microsoft .NET and is used in several applications.

  A SQL Server Integration Services (SSIS) package is importing data into a Microsoft Azure SQL Database. Some of the data must be encrypted with the proprietary encryption algorithm.

  You need to design the implementation strategy to meet the requirements while minimizing development and deployment effort and maximizing data flow performance.

  What should you do?

  A. Use an SSIS Script transformation that uses the custom assembly to encrypt the data when inserting it.
  B. Use an SSIS Script task that uses the custom assembly the encrypt the data when inserting it.
  C. Create a SQL Common Language Runtime (SQLCLR) stored procedure that uses the custom assembly to encrypt the data, deploy it in the Microsoft Azure SQL Database and use it when inserting data.
  D. Create a SQL Common Language Runtime (SQLCLR) function that uses the custom assembly to encrypt the data, deploy it in the Microsoft Azure SQL Database and use it when inserting data.
  A. Use an SSIS Script transformation that uses the custom assembly to encrypt the data when inserting it.

• Question 160:

  Which statements best describe SQL Server Development Tools (SSDT)? (Choose all that apply.)

  A. SSDT is an extension of the SQL Server Management Studio that can be used to create SSIS packages by means of a special wizard.
  B. SSDT is a special edition of the SQL Server Management Studio, designed to provide an improved user experience to developers who are not particularly familiar with database administration.
  C. SSDT is a special edition of Visual Studio, distributed with SQL Server 2012, providing a rich database development tool set.
  D. SSDT is a new service in SQL Server 2012 that can be used to perform SQL Server maintenance tasks, such as data movements and similar data management processes.
  C. SSDT is a special edition of Visual Studio, distributed with SQL Server 2012, providing a rich database development tool set.

  ---

  References: https://www.codeproject.com/Articles/451750/Installing-SQL-Server-Data-Tools-SSDT