[Free] 2017(Oct) EnsurePass Braindumps Microsoft 70-767 Dumps with VCE and PDF 101-110

Ensurepass.com : Ensure you pass the IT Exams
2017 Oct 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: 101 – (Topic 2)

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

The package uses a Foreach container to process text files found in a folder. The package must be deployed to a single server by using the Project Deployment model.

Multiple SQL Server Agent jobs call the package. Each job is executed on a different schedule.

Each job passes a different folder path to the package.

You need to configure the package to accept the folder path from each job. Which package configuration should you use?

  1. Parent Package Variable

  2. XML Configuration File

  3. Environment Variable

  4. .dtsConfig file

  5. Registry Entry

Answer: C

Question No: 102 – (Topic 2)

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 SQL Azure. 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?

  1. Audit

  2. Source Assistant

  3. Script Task

  4. Conditional Split

Answer: D Explanation:

http://msdn.microsoft.com/en-us/library/ms137640.aspx http://msdn.microsoft.com/en-us/library/ms141150.aspx http://msdn.microsoft.com/en-us/library/ff929138.aspx http://msdn.microsoft.com/en-us/library/ff929116.aspx

Question No: 103 – (Topic 2)

You are developing a project that contains multiple SQL Server Integration Services (SSIS) packages. The packages will be deployed to the SSIS catalog. One of the steps in each package accesses an FTP site to download data files.

You create project parameters to store the username and password that are used to access the FTP site.

You need to ensure that the username and password values are encrypted when they are deployed.

What should you do?

  1. Convert the parameters to package parameters.

  2. Set the Sensitive property of the parameters to True.

  3. Set the ProtectionLevel property of the package to EncryptSensitiveWithPassword.

  4. Convert the project to the Legacy Deployment model.

Answer: B

Question No: 104 – (Topic 2)

You install a SQL Server 2016 database engine instance on a production server. A month later, you install SQL Server 2016 Integration Services (SSIS).

You must develop an SSIS project and deploy it to the server by using the Project Deployment model.

Operations log records that are outside the configured retention period must be cleaned automatically.

You need to create the SSIS catalog on the production server and ensure that the operations log cleaning requirement is met.

What should you do? (Each correct answer presents part of the solution. Choose all that apply.)

  1. Enable CLR Integration.

  2. Enable FILESTREAM with Full Access.

  3. Enable the Resource Governor.

  4. Change the recovery mode of the msdb database to FULL.

  5. Change the Server-wide Default Logging Level in SSISDB to Verbose.

  6. Start the SQL Server Browser service.

  7. Start the SQL Server Agent service.

Answer: A,G

Question No: 105 DRAG DROP – (Topic 2)

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

You must combine two data sources together by using the ProductID column to provide complete details for each record. The data retrieved from each data source is sorted in ascending order by the ProductID column.

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

Question No: 106 – (Topic 2)

You are developing a SQL Server Integration Services (SSIS) package to load data into a Windows Azure SQL Database database. The package consists of several data flow tasks.

The package has the following auditing requirements:

->If a data flow task fails, a Transact-SQL (T-SQL) script must be executed.

->The T-SQL script must be executed only once per data flow task that fails, regardless of the nature of the error.

You need to ensure that auditing is configured to meet these requirements. 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.va!idate_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 OnTaskFailed event text to the table.

Answer: C

Question No: 107 – (Topic 2)

You are implementing a SQL Server Integration Services (SSIS) package that loads data hosted in a SQL Azure 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?

  1. Add an OnError event handler to the SSIS project.

  2. Open a command prompt and execute the package by using the SQL Log provider and running the dtexecui.exe utility.

  3. Use an msi file to deploy the package on the server.

  4. Open a command prompt and run the gacutil command.

  5. Run the dtutil command to deploy the package to the SSIS catalog and store the configuration in SQL Server.

  6. Open a command prompt and run the dtutil /copy command.

  7. Create a reusable custom logging component and use it in the SSIS project.

  8. Configure the SSIS solution to use the Project Deployment Model.

  9. Configure the output of a component in the package data flow to use a data tap.

  10. Open a command prompt and run the dtexec /rep /conn command.

  11. Open a command prompt and run the dtexec /dumperror /conn command.

Answer: I

Explanation:

References:

http://technet.microsoft.com/en-us/library/hh230989.aspx

http://www.rafael-salas.com/2016/01/ssis-2016-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 No: 108 – (Topic 2)

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

The package must meet the following requirements:

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

鈥he Connection Managers used in the package must be configurable without editing the package.

鈥he deployment procedure must be automated as much as possible. You need to set up a deployment strategy that meets the requirements. What should you do?

  1. Use the gacutil command.

  2. Use the dtutil /copy command.

  3. Use the Project Deployment Wizard.

  4. Create an OnError event handler.

  5. Create a reusable custom logging component.

  6. Run the package by using the dtexec /rep /conn command.

  7. Run the package by using the dtexec /dumperror /conn command.

  8. Run the package by using the dtexecui.exe utility and the SQL Log provider.

  9. Add a data tap on the output of a component in the package data flow.

  10. Deploy the package by using an msi file.

  11. Deploy the package to the Integration Services catalog by using dtutil and use SQL Server to store the configuration.

    Answer: B

    Question No: 109 DRAG DROP – (Topic 2)

    You are creating a SQL Server Integration Services (SSIS) package to populate a fact table from a source table. The fact table and source table are located in a SQL Azure database. The source table has a price field and a tax field. The OLE DB source uses the data access mode of Table.

    You have the following requirements:

    鈥he fact table must populate a column named TotalCost that computes the sum of the price and tax columns.

    鈥efore the sum is calculated, any records that have a price of zero must be discarded. You need to create the SSIS package in SQL Server Data Tools.

    In what sequence should you order four of the listed components for the data flow task? (To answer, move the appropriate components from the list of components to the answer area and arrange them in the correct order.)

    Ensurepass 2017 PDF and VCE

    Answer:

    Ensurepass 2017 PDF and VCE

    Explanation:

    1. OLEDB Source

    2. Conditional Split

    3. Derived Column

    4. OLEDB Destination

      Question No: 110 DRAG DROP – (Topic 2)

      A new dedicated server is used to execute resource-intensive SQL Server Integration Services (SSIS) 2016 packages. The environment that you are deploying the packages to has the following constraints:

      ->The operating system is Windows Server 2008 R2.

      ->The SSIS packages are stored in the SSIS catalog.

      ->Some of these SSIS packages use 32-bit custom components.

      You need to install only the components that are required to deploy and run the packages on the new server.

      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:

      Box 1:

      Ensurepass 2017 PDF and VCE

      Box 2:

      Ensurepass 2017 PDF and VCE

      Box 3:

      Ensurepass 2017 PDF and VCE

      Note: Step 1:

      Step 2:

      If you require 32-bit versions of the Integration Services runtime and tools to run certain packages in 32-bit mode, you must also install SQL Server Data Tools (SSDT).

      Note:

      For a complete installation of Integration Services, together with the tools and documentation for developing and managing packages, select both Integration Services and the following Shared Features:

Leave a Reply

Your email address will not be published. Required fields are marked *

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