[Free] 2017(Sep) EnsurePass Dumpsleader Microsoft 70-767 Dumps with VCE and PDF 81-90

Ensurepass.com : Ensure you pass the IT Exams
2017 Sep Microsoft Official New Released 70-767
100% Free Download! 100% Pass Guaranteed!
http://www.EnsurePass.com/70-767.html

Implementing a Data Warehouse using SQL

Question No: 81 – (Topic 2)

You administer a Microsoft SQL Server 2016 database. The database contains a table named Employee. Part of the Employee table is shown in the exhibit. (Click the Exhibit button.)

Ensurepass 2017 PDF and VCE

Ensurepass 2017 PDF and VCE

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 use an identity specification to include a seed of 1,000 and an increment of 1?

  1. DateHired

  2. DepartmentID

  3. EmployeeID

  4. EmployeeNum

  5. FirstName

  6. JobTitle

  7. LastName

  8. MiddleName

  9. ReportsToID

Answer: C

Question No: 82 DRAG DROP – (Topic 2)

You are developing a SQL Server Integration Services (SSIS) package that imports unsorted data into a data warehouse hosted on SQL Azure.

You have the following requirements:

鈥 destination table must contain all of the data in two source tables.

鈥uplicate records must be inserted into the destination table.

You need to develop a data flow that imports the data while meeting the requirements.

How should you develop the data flow? (To answer, drag the appropriate transformation from the list of transformations to the correct location in the answer area.)

Ensurepass 2017 PDF and VCE

Answer:

Ensurepass 2017 PDF and VCE

Explanation:

Ensurepass 2017 PDF and VCE

References:

http://msdn.microsoft.com/en-us/library/ms141703.aspx http://msdn.microsoft.com/en-us/library/ms141775.aspx http://msdn.microsoft.com/en-us/library/ms141020.aspx http://msdn.microsoft.com/en-us/library/ms140182.aspx

Question No: 83 – (Topic 2)

You are developing a SQL Server Integration Services (SSIS) project with multiple packages to copy data to a Windows Azure SQL Database database.

An automated process must validate all related Environment references, parameter data types, package references, and referenced assemblies. The automated process must run on a regular schedule.

You need to establish the automated validation process by using the least amount of administrative effort.

What should you do?

  1. Use an event handler for OnError for the package.

  2. Use an event handler for OnError for each data flow task.

  3. Use an event handler for OnTaskFailed for the package.

  4. View the job history for the SQL Server Agent job.

  5. View the All Messages subsection of the All Executions report for the package.

  6. Store the System::SourceID variable in the custom log table.

  7. Store the System::ServerExecutionID variable in the custom log table.

  8. Store the System::ExecutionInstanceGUID variable in the custom log table.

  9. Enable the SSIS log provider for SQL Server for OnError in the package control flow.

  10. Enable the SSIS log provider for SQL Server for OnTaskFailed in the package control flow.

  11. Deploy the project by using dtutil.exe with the /COPY DTS option.

  12. Deploy the project by using dtutil.exe with the /COPY SQL option.

  13. Deploy the .ispac file by using the Integration Services Deployment Wizard.

  14. Create a SQL Server Agent job to execute the SSISDB.catalog.validate_project stored procedure.

  15. Create a SQL Server Agent job to execute the SSISDB.catalog.validate_package stored procedure.

  16. Create a SQL Server Agent job to execute the

    SSISDB.catalog.create_execution and SSISDB.catalog.start_execution stored procedures.

  17. Create a table to store error information. Create an error output on each data flow destination that writes OnError event text to the table.

  18. Create a table to store error information. Create an error output on each data flow destination that writes OnTaskFailed event text to the table.

Answer: N

Question No: 84 – (Topic 2)

You maintain a SQL Server Integration Services (SSIS) package. The package was developed by using SQL Server 2008 Business Intelligence Development Studio (BIDS).

The package includes custom scripts that must be upgraded.

You need to upgrade the package to SQL Server 2016. Which tool should you use?

  1. SQL Server dtexec utility (dtexec.exe)

  2. SQL Server DTExecUI utility (dtexecui.exe)

  3. SSIS Upgrade Wizard in SQL Server Data Tools

  4. SQL Server Integration Services Deployment Wizard

Answer: C

Explanation: Use the SSIS Package Upgrade Wizard to upgrade SQL Server 2005 Integration Services (SSIS) packages and SQL Server 2008 Integration Services (SSIS) packages to the package format for the current (2016) release of SQL Server Integration Services.

Question No: 85 – (Topic 2)

You are designing a fact table in a SQL Server database. The fact table must meet the following requirements:

鈥nclude a columnstore index.

鈥llow users to choose up to 10 dimension tables and up to five facts at one time.

鈥aximize performance of queries that aggregate measures by using any of the 10 dimensions.

鈥upport billions of rows.

鈥se the most efficient design strategy.

You need to design the fact table to meet the requirements. What should you do? (More than one answer choice may achieve the goal. Select the BEST answer.)

  1. Design a fact table with 5 dimensional key columns and 10 measure columns. Place the columnstore index on the dimensional key columns.

  2. Design a fact table with 5 dimensional key columns and 10 measure columns. Place the

    columnstore index on the measure columns.

  3. Design a fact table with 10 dimensional key columns and 5 measure columns. Place the columnstore index on the dimensional key columns and the measure columns.

  4. Design a fact table with 10 dimensional key columns and 5 measure columns. Place the columnstore index on only the measure columns.

    Answer: C

    Question No: 86 DRAG DROP – (Topic 2)

    You are validating whether a SQL Server Integration Services (SSIS) package named Master.dtsx in the SSIS catalog is executing correctly.

    You need to display the number of rows in each buffer passed between each data flow component of the package.

    Which three actions should you perform in sequence? (To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct order.)

    Ensurepass 2017 PDF and VCE

    Answer:

    Ensurepass 2017 PDF and VCE

    Explanation:

    1. Run with verbose settings

    2. Get Execution ID from .executions

    3. Get stats from .execution_data_statistics ( rows_sent ) Ref: http://msdn.microsoft.com/en-us/library/hh230986.aspx

      Question No: 87 – (Topic 2)

      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:

      鈥nsure that the deployment model supports changing the content of connection strings by using parameters at execution time.

      鈥nsure that the deployment model automatically starts from calls to the catalog.start_execution stored procedure in the SSISDB database.

      鈥aximize performance at execution time.

      鈥inimize development effort.

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

      1. Use a project deployment model. Modify connection manager properties to use project parameters. Ensure that the SSISDB database is created.

      2. Use a project 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.

      3. Use a package deployment model. Use a SQL Server package configuration with a common filter. Change the contents of the SSIS Configurations table at runtime.

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

Answer: A

Question No: 88 HOTSPOT – (Topic 2)

You are designing a SQL Server Integration Services (SSIS) package configuration strategy.

The package configuration must meet the following requirements:

鈥nclude multiple properties in a configuration.

鈥upport several packages with different configuration settings.

You need to select the appropriate configuration. Which configuration type should you use? To answer, select the appropriate option from the drop-down list in the dialog box.

Ensurepass 2017 PDF and VCE

Answer:

Ensurepass 2017 PDF and VCE

Question No: 89 – (Topic 2)

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?

  1. In the SSISDB.[catalog], query the .[executions] view.

  2. In the SSISDB.[catalog] , query the [event_messages] view.

  3. In SQL Server Management Studio, view the SQL Agent Job History.

  4. In SQL Server Management Studio, view the SQL Agent Error Log.

  5. In SQL Server Management Studio, view the SQL Server Log.

Answer: A

Question No: 90 DRAG DROP – (Topic 2)

You are loading a dataset into SQL Server. The dataset contains numerous duplicates for the Artist and Song columns.

The values in the Artist column in the dataset must exactly match the values in the Artist domain in the knowledge base. The values in the Song column in the dataset can be a close match with the values in the Song domain.

You need to use SQL Server Data Quality Services (DQS) to define a matching policy rule to identify duplicates.

How should you configure the Rule Editor? (To answer, drag the appropriate answers to the answer area.)

Ensurepass 2017 PDF and VCE

Answer:

Ensurepass 2017 PDF and VCE

100% Ensurepass Free Download!
Download Free Demo:70-767 Demo PDF
100% Ensurepass Free Guaranteed!
Download 2017 EnsurePass 70-767 Dumps

EnsurePass ExamCollection Testking
Lowest Price Guarantee Yes No No
Up-to-Dated Yes No No
Real Questions Yes No No
Explanation Yes No No
PDF VCE Yes No No
Free VCE Simulator Yes No No
Instant Download Yes No No

2017 EnsurePass IT Certification PDF and VCE

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.