[Free] 2019(Nov) EnsurePass Microsoft 70-767 Dumps with VCE and PDF 171-180

Get Full Version of the Exam
http://www.EnsurePass.com/70-767.html

Question No.171

You have a SQL server integration Services (SSIS) package named package1. You discover that the Data Flow task for Package1 runs slower than expected.

You need to reduce the amount of time required to run the Data Flow task. Which two actions should you perform? (Select Two)

  1. Configure the package to run by using the CallerInfo option.

  2. Modify the DefaultBufferSize package setting.

  3. Modify the ForceExecutionResult Package setting.

  4. Modify the DefaultBufferMaxRows package setting.

  5. Configure the package to run by using MaxConcurrent option.

Correct Answer: DE

Question No.172

You are implementing a SQL Server Integration Services (SSIS) package that imports Microsoft Excel workbook data into a Windows Azure SQL Database database. The package has been deployed to a production server that runs Windows Server 2008 R2 and SQL Server 2016.

The package fails when executed on the production server.

You need to ensure that the package can load the Excel workbook data without errors. You need to use the least amount of administrative effort to achieve this goal.

What should you do?

  1. Install a 64-bit ACE driver and execute the package by using the 64-bit run-time option.

  2. Enable Address Windowing Extensions (AWE) for the local SQL Server instance.

  3. Replace the SSIS Excel source with a SSIS Flat File source.

  4. Install a 64-bit ACE driver and replace the Excel source with an OLE DB source.

Correct Answer: D

Explanation:

XLSX files, the new file type in Excel 2007/2010 cannot be opened with Excel Source/Destination in SSIS. To use XLSX in SSIS we need to install ACE driver and use OLE DB Source/Destination to read read/write data in XLSX files.

ACE is a set of components that facilitate the transfer of data between existing Microsoft Office files such as Microsoft Office Access (*.mdb and *.accdb) files and Microsoft Office Excel 7 (*.xls,

*.xlsx, and *.xlsb) files to Microsoft SQL Server. If the SQL Server or your development box does not have ACE driver then install that 1st.

Note:

The most efficient way to link to SQL Server is using an OLEDB connection manager.

Incorrect: Not B:

Address Windowing Extensions (AWE) is a Microsoft Windows application programming interface that allows a 32-bit software application to access more physical memory than it has virtual address space.

Address Windowing Extensions (AWE) is a set of extensions that allows an application to quickly manipulate physical memory greater than 4GB. Certain data-intensive applications, such as database management systems and scientific and engineering software, need access to very large caches of data. In the case of very large data sets, restricting the cache to fit within an application#39;s 2GB of user address space is a severe restriction.

Question No.173

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 Integration Services Deployment Wizard

  2. SQL Server Configuration Manager

  3. SSIS Upgrade Wizard in SQL Server Management Studio

  4. SSIS Upgrade Wizard in SQL Server 2008 BIDS

Correct Answer: C

Explanation:

You can upgrade packages that were created in earlier versions of Integration Services to the Integration Services format that SQL Server 2016 uses. SQL Server provides the SSIS Package Upgrade Wizard to help in this process. Because you can configure the wizard to backup up your original packages, you can continue to use the original packages if you experience upgrade difficulties.

You can run the SSIS Package Upgrade Wizard from SQL Server Data Tools (SSDT), from SQL Server Management Studio, or at the command prompt.

Note:

When you upgrade an instance of SQL Server 2005 or SQL Server 2008 to the current release of SQL Server, your existing SQL Server 2008 Integration Services (SSIS) packages are not automatically upgraded to the package format that the current release SQL Server Integration Services uses. You will have to select an upgrade method and manually upgrade your packages.

Question No.174

You are designing a data warehouse that uses SQL Server 2016. You are preparing to update the contents of a fact table that contains a non-clustered columnstore index.

You need to run an update statement against the table. What should you do first?

  1. Pause the columnstore index.

  2. Change the recovery model of the database to Bulk-logged.

  3. Change the non-clustered columnstore index to be a clustered columnstore index.

  4. Drop the columnstore index.

Correct Answer: D

Question No.175

You are administering SQL Server Integration Services (SSIS) permissions on a production server that runs SQL Server 2016.

Quality Assurance (QA) testers in the company must have permission to perform the following tasks on specific projects:

image

View and validate projects and packages

image

image

View Environments and Environment variables Execute packages

You need to grant the minimum possible privileges to the QA testers.

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

  1. In the SSISDB database, add QA Tester logons to the ssis_admin role.

  2. In the msdb database, add QA Tester logons to the db_ssisoperator role.

  3. Grant Modify permission in the projects to the QA Tester logons.

  4. Grant Read permission in the SSIS catalog folder, the projects, and the Environments to the QA Tester logons.

  5. Grant Execute permission in the projects to the QA Tester logons.

  6. In the msdb database, add QA Tester logons to the db_ssisItduser role.

Correct Answer: BD

Question No.176

You are developing a SQL Server Integration Services (SSIS) project to read and write data from a Windows Azure SQL Database database to a server that runs SQL Server 2016.

The connection will be used by data flow tasks in multiple SSIS packages. The address of the target Windows Azure SQL Database database will be provided by a project parameter.

You need to create a solution to meet the requirements by using the least amount of administrative effort and maximizing data flow performance.

What should you do?

  1. Use an SSIS Script task that uses the custom assembly to parse the text data when inserting it.

  2. Use an SSIS Script transformation that uses the custom assembly to parse the text data when inserting it.

  3. Create a SQL Common Language Runtime (SQLCLR) function that uses the custom assembly to parse the text data, deploy it in the Windows Azure SQL Database database, and use it when inserting data.

  4. Create a SQL Common Language Runtime (SQLCLR) stored procedure that uses the custom assembly to parse the text data, deploy it in the Windows Azure SQL Database database, and use it when inserting data.

Correct Answer: A

Question No.177

You are performance tuning a SQL Server Integration Services (SSIS) package to load sales data from a source system into a data warehouse that is hosted on Windows Azure SQL Database.

The package contains a data flow task that has seven source-to-destination execution trees. Only three of the source-to-destination execution trees are running in parallel.

You need to ensure that all the execution trees run in parallel.

What should you do?

  1. Set the EngineThreads property of the data flow task to 7.

  2. Set the MaxConcurrentExcecutables property of the package to 7.

  3. Create seven data flow tasks that contain one source-to-destination execution tree each.

  4. Place the data flow task in a For Loop container that is configured to execute seven times.

Correct Answer: A

Question No.178

You develop a SQL Server Integration Services (SSIS) package in a project by using the Project Deployment Model. It is regularly executed within a multi-step SQL Server Agent job.

You make changes to the package that should improve performance.

You need to establish if there is a trend in the durations of the next 10 successful executions of the package. You need to use the least amount of administrative effort to achieve this goal.

What should you do?

  1. After 10 executions, in SQL Server Management Studio, view the Execution Performance subsection of the All Executions report for the package.

  2. Configure the package to send you an email upon completion that includes information about the duration of the package. After 10 executions, view the emails.

  3. Enable logging to the Application Event Log in the package control flow for the OnInformation event. After 10 executions, view the Application Event Log.

  4. Enable logging to the Application Event Log in the package control flow for the OnPostExecute event. After 10 executions, view the Application Event Log.

Correct Answer: A

Explanation:

The All Executions Report displays a summary of all Integration Services executions that have been performed on the server. There can be multiple executions of the sample package. Unlike the Integration Services Dashboard report, you can configure the All Executions report to show executions that have started during a range of dates. The dates can span multiple days, months, or years.

The report displays the following sections of information. Filter

Shows the current filter applied to the report, such as the Start time range. Execution Information

Shows the start time, end time, and duration for each package execution. You can view a list of the parameter values that were used with a package execution, such as values that were passed to a child package using the Execute Package task.

Question No.179

You are completing the installation of the Data Quality Server component of SQL Server Data Quality Services (DQS).

You need to complete the post-installation configuration. What should you do?

  1. Run the Data Quality Server Installer.

  2. Install the data providers that are used for data refresh.

  3. Run the dbimpexp.exe command.

  4. Install the Analysis Services OLE DB Provider.

Correct Answer: A

Question No.180

You are developing a SQL Server Integration Services (SSIS) project that contains a project Connection Manager and multiple packages.

All packages in the project must connect to the same database. The server name for the database must be set by using a parameter named ServerParam when any package in the project is executed.

You need to develop this project with the least amount of development effort.

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

  1. Set the Sensitive property of the parameter to True.

  2. Edit each package Connection Manager. Set the ServerName property to

    @[$Project::ServerParam].

  3. Edit the project Connection Manager in Solution Explorer. Set the ServerName property to

    @[$Project::ServerParam].

  4. Create a project parameter named ServerName.

  5. Create a package parameter named ServerName in each package.

  6. Set the Required property of the parameter to True.

Correct Answer: CDF

Explanation:

C: From question: quot; The server name for the database must be set by using a parameter named ServerParam when any package in the project is executed.quot;

D: SSIS 2016 has introduced the concept of Project level connection managers. An SSIS project is generally more than one package. To simplify lives, the SSIS team now allows for the sharing of common resources across projects, connection managers being one of those resources.

F: When a parameter is marked as required, a server value or execution value must be specified for that parameter. Otherwise, the corresponding package does not execute. Although the parameter has a default value at design time, it will never be used once the project is deployed.

Note:

Integration Services (SSIS) parameters allow you to assign values to properties within packages at the time of package execution. You can create project parameters at the project level and package parameters at the package level. Project parameters are used to supply any external input the project receives to one or more packages in the project. Package parameters allow you to modify package execution without having to edit and redeploy the package.

Reference: Integration Services (SSIS) Parameters

Get Full Version of the Exam
70-767 Dumps
70-767 VCE and PDF

Leave a Comment

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