Tips to Pass 70-767 Exam (71 to 80)

Act now and download your Microsoft 70-767 test today! Do not waste time for the worthless Microsoft 70-767 tutorials. Download Up to the immediate present Microsoft Implementing a SQL Data Warehouse (beta) exam with real questions and answers and begin to learn Microsoft 70-767 with a classic professional.


Free VCE & PDF File for Microsoft 70-767 Real Exam
(Full Version!)

Pass on Your First TRY 100% Money Back Guarantee Realistic Practice Exam Questions

Free Instant Download NEW 70-767 Exam Dumps (PDF & VCE):
Available on:

Q71. 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?

A. Parent Package Variable

B. XML Configuration File

C. Environment Variable

D. .dtsConfig file

E. Registry Entry

Answer: C

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

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

A. Enable XP Command Shell.

B. Enable CLR Integration.

C. Enable OLE Automation.

D. Start the SQL Server Browser service.

E. Enable Cross Database Ownership Chaining

F. Start the SQL Server Agent service

G. Enable Ad Hoc Remote Queries.

Answer: B,F

Explanation: Ref:


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:

•The fact table must populate a column named TotalCost that computes the sum of the price and tax columns.

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



1. OLEDB Source

2. Conditional Split

3. Derived Column

4. OLEDB Destination


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

The package uses custom functionality that accesses a SQL Server database. The custom functionality must be implemented by using Language Integrated Query (LINQ).

You need to ensure that the LINQ code can be debugged at design time.

What should you select from the SSIS Toolbox? (To answer, drag the appropriate item to the correct location in the answer area.)


Q75. You are creating a SQL Server Integration Services (SSIS) package to retrieve product data from two different sources. One source is hosted in a SQL Azure database. Each source contains products for different distributors.

Products for each distributor source must be combined for insertion into a single product table destination.

You need to select the appropriate data flow transformation to meet this requirement.

Which transformation types should you use? (Each answer represents a complete solution. Choose all that apply.)

A. Slowly Changing Dimension

B. pivot

C. Lookup

D. Union All

E. Merge

Answer: D,E

Q76. You are using SQL Server Data Tools to develop a SQL Server Integration Services (SSIS) project.

The first package that you create in this project contains a package connection that accesses a flat file. Additional packages in the project must also access this file.

You need to define and reuse the flat file connection in all project packages. What should you do?

A. Convert the package Connection Manager in the first package to a project Connection Manager.

B. Copy the package Connection Manager and paste it into the second package.

C. Convert the project to the Package Deployment model.

D. Set the ProtectionLevel property of the package Connection Manager to DontSaveSensitive to reuse the flat file connection.

Answer: A


A SQL Server Integration Services (SSIS) project has been deployed to the SSIS catalog. The project includes a project Connection Manager to connect to the data warehouse.

The SSIS catalog includes two Environments:

✑  Development

✑ QA

Each Environment defines a single Environment Variable named ConnectionString of type string. The value of each variable consists of the connection string to the development or QA data warehouses.

You need to be able to execute deployed packages by using either of the defined Environments.

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


Q78. 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?

A. INSERT INTO dbo.Table (variablevalue) VALUES (@StringVar)

B. INSERT INTO dbo.Table (variablevalue) VALUES ($Project::StringVar)

C. INSERT INTO dbo.Table (variablevalue) VALUES (?)

D. INSERT INTO dbo.Table (variablevalue) VALUES ($Package::StringVar)

Answer: C


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



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


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:

* SQL Server Data Tools to install the tools for designing packages.

* Management Tools – Complete to install SQL Server Management Studio for managing packages.

* Client Tools SDK to install managed assemblies for Integration Services programming.

Step 3: How to Modify the Service Configuration File to Connect to a Named Instance of SQL Server

To connect to a named instance of SQL Server, you must modify the service configuration file, MsDtsSrvr.ini.xml.


You are designing a package control flow. The package moves sales order data from a SQL Azure transactional database to an on-premise reporting database. The package will run several times a day, while new sales orders are being added to the transactional database.

The current design of the package control flow is shown in the answer area. (Click the Exhibit button.)

The Insert New Orders Data Flow task must meet the following requirements:

✑ Usage of the tempdb database should not be impacted.

✑ Concurrency should be maximized, while only reading committed transactions.

✑ If the task fails, only that task needs to be rolled back.

You need to configure the Insert New Orders Data Flow task to meet the requirements.

How should you configure the transaction properties? To answer, select the appropriate setting or settings in the answer area.


IsolationLevel = ReadCommited. TransactionOption = Required