Valid Microsoft 70-767 dumps questions shared by Pass4itsure for helping to pass the Microsoft 70-767 exam! Get the newest Pass4itsure 70-767 exam dumps with VCE and PDF here: https://www.pass4itsure.com/70-767.html (402 Q&As Dumps).
Suitable for 70-767 complete Microsoft learning pathway
The content is rich and diverse, and learning will not become boring. You can learn in multiple ways through theMicrosoft 70-767 exam.
- Download
- Watch the video
- Answer practice questions, the actual test
Free Microsoft 70-767 dumps download
[PDF] Free Microsoft 70-767 dumps download https://drive.google.com/file/d/1NZ9sJeIOEZUW7iu-kvQJWg0PXdPj1WoF/view?usp=sharing
Pass4itsure offers the latest Microsoft 70-767 practice test free of charge 1-13
QUESTION 1
You develop a SQL Server Integration Services (SSIS) package that imports Windows Azure SQL Database data into a
data warehouse every night.
The Windows Azure SQL Database data contains many misspellings and variations of abbreviations. To import the
data, a developer used the Fuzzy Lookup transformation to choose the closest-matching string from a reference table
of
allowed values. The number of rows in the reference table is very large.
If no acceptable match is found, the Fuzzy Lookup transformation passes a null value.
The current setting for the Fuzzy Lookup similarity threshold is 0.50.
Many values are incorrectly matched.
You need to ensure that more accurate matches are made by the Fuzzy Lookup transformation without degrading
performance.
What should you do?
A. Change the similarity threshold to 0.40.
B. Decrease the maximum number of matches per lookup.
C. Change the similarity threshold to 0.85.
D. Increase the maximum number of matches per lookup.
Correct Answer: C
Explanation: * Similarity threshold Set the similarity threshold at the component level by using the slider. The closer the
value is to 1, the closer the resemblance of the lookup value to the source value must be to qualify as a match.
Increasing the threshold can improve the speed of matching since fewer candidate records need to be considered.
QUESTION 2
You are creating a Data Quality Services (DQS) solution. You must provide statistics on the accuracy of the data.
You need to use DQS profiling to obtain the required statistics.
Which DQS activity should you use?
A. Cleansing
B. Knowledge Discovery
C. SQL Profiler
D. Matching Rule Definition
Correct Answer: A
QUESTION 3
Note: This question is part of a series of questions that present the same scenario. Each question in the series contains
a unique solution that might meet the stated goals. Some question sets might have more than one correct solution,
while
others might not have a correct solution.
After you answer a question in this sections, you will NOT be able to return to it. As a result, these questions will not
appear in the review screen.
You have the following line-of-business solutions:
ERP system
Online WebStore
Partner extranet
One or more Microsoft SQL Server instances support each solution. Each solution has its own product catalog. You
have an additional server that hosts SQL Server Integration Services (SSIS) and a data warehouse. You populate the
data
warehouse with data from each of the line-of-business solutions. The data warehouse does not store primary key values
from the individual source tables.
The database for each solution has a table named Products that stored product information. The Products table in each
database uses a separate and unique key for product records. Each table shares a column named ReferenceNr
between
the databases. This column is used to create queries that involve more than once solution.
You need to load data from the individual solutions into the data warehouse nightly. The following requirements must be
met:
If a change is made to the ReferenceNr column in any of the sources, set the value of IsDisabled to True and create a
new row in the Products table.
If a row is deleted in any of the sources, set the value of IsDisabled to True in the data warehouse.
Solution: Perform the following actions:
Enable the Change Tracking feature for the Products table in the three source databases.
Query the CHANGETABLE function from the sources for the deleted rows.
Set the IsDIsabled column to True on the data warehouse Products table for the listed rows.
Does the solution meet the goal?
A. Yes
B. No
Correct Answer: B
We must check for updated rows, not just deleted rows.
References: https://www.timmitchell.net/post/2016/01/18/getting-started-with-change-tracking-in-sql-server/
QUESTION 4
You are developing a SQL Server Integration Services (SSIS) package to load data into a data warehouse. The
package consists of several data flow tasks.
The package experiences intermittent errors in the data flow tasks.
If any data flow task fails, all package error information must be captured and written to a SQL Server table by using an
OLE DB connection manager.
You need to ensure that the package error information is captured and written to the table.
What should you do?
A. Use an event handler for OnError for the package.
B. Use an event handler for OnError for each data flow task.
C. Use an event handler for OnTaskFailed for the package.
D. View the job history for the SQL Server Agent job.
E. View the All Messages subsection of the All Executions report for the package.
F. Store the System::SourceID variable in the custom log table.
G. Store the System::ServerExecutionID variable in the custom log table.
H. Store the System::ExecutionInstanceGUID variable in the custom log table.
I. Enable the SSIS log provider for SQL Server for OnError in the package control flow.
J. Enable the SSIS log provider for SQL Server for OnTaskFailed in the package control flow.
K. Deploy the project by using dtutil.exe with the /COPY DTS option.
L. Deploy the project by using dtutil.exe with the /COPY SQL option.
M. Deploy the .ispac file by using the Integration Services Deployment Wizard.
N. Create a SQL Server Agent job to execute the SSISDB.catalog.validate_project stored procedure.
O. Create a SQL Server Agent job to execute the SSISDB.catalog.validate_package stored procedure.
P. Create a SQL Server Agent job to execute the SSISDB.catalog.create_execution and
SSISDB.catalog.start_execution stored procedures.
Q. Create a table to store error information. Create an error output on each data flow destination that writes OnError
event text to the table.
R. Create a table to store error information. Create an error output on each data flow destination that writes
OnTaskFailed event text to the table.
Correct Answer: I
QUESTION 5
Note: This question is 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 have a database named DB1 that has change data capture enabled.
A Microsoft SQL Server Integration Services (SSIS) job runs once weekly. The job loads changes from DB1 to a data
warehouse by querying the change data capture tables.
You discover that the job loads changes from the previous three days only.
You need re ensure that the job loads changes from the previous week.
Which stored procedure should you execute?
A. catalog.deploy_project
B. catalog.restore_project
C. catalog.stop.operation
D. sys.sp_cdc.addJob
E. sys.sp.cdc.changejob
F. sys.sp_cdc_disable_db
G. sys.sp_cdc_enable_db
H. sys.sp_cdc.stopJob
Correct Answer: A
catalog.deploy_project deploys a project to a folder in the Integration Services catalog or updates an existing project
that has been deployed previously. References: https://docs.microsoft.com/en-us/sql/integration-services/system-storedprocedures/catalog-deploy-project- ssisdb-database
QUESTION 6
You have a database named DB1. You create a Microsoft SQL Server Integration Services (SSIS) package that
incrementally imports data from a table named Customers. The package uses an OLE DB data source for connections
to DB1.
The package defines the following variables. To support incremental data loading, you create a table by running the
following Transact-SQL segment:
You need to create a DML statements that updates the LastKeyByTable table.
How should you complete the Transact-SQL statement? To answer, select the appropriate Transact-SQL segments in
the dialog box in the answer area.
Hot Area:
Correct Answer:
QUESTION 7
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:
Correct Answer: D
Explanation: References:
http://msdn.microsoft.com/en-us/library/ms141003.aspx http://msdn.microsoft.com/en-us/library/ms140355.aspx
http://msdn.microsoft.com/en-us/library/cc280502.aspx
QUESTION 8
HOTSPOT
You are using Microsoft SQL Server data Tools (SSID) to create a SQL Server Information Services (SSIS) package.
The package contains a single Data Flow task as shown in the Data Flow exhibit. (Click the Data Flow tab.) 
Project properties are provided in the Project Properties exhibit. (Click the Project Properties tab.)
The data flow imports a remote Microsoft Excel file if it exists. You have all of the appropriate permissions to access the
file. The package fails to complete. The error message is shown in the Execution Results exhibit. (Click the Execution
Results tab.) Execution Results
These steps should be tried in the following order until the issue is resolved. Make sure the Jet Data Manager and Excel
have the same bit-rate. If Excel is 32-bit, you will need to install the 32-bit version of the Jet Data Manager. If Excel is
64-bit, you will need to install the 64-bit version of the Jet Data Manager. Install Microsoft Access Database Engine
2010 Redistributable. These components can be found here: Microsoft Access Database Engine 2010 Redistributable
Ensure that you explicitly run the executable as an administrator Install 2007 Office System Driver: Data Connectivity
Components. These components can be automatically downloaded here: 2007 Office System Driver: Data Connectivity
Components Ensure that you explicitly run the executable as an administrator
References: https://support.jetglobal.com/hc/en-us/articles/219401847-Error-The-Microsoft-ACE-OLEDB-12-0-provideris-not-registered
QUESTION 9
A SQL Server Integration Services (SSIS) package on a computer is failing periodically in production. The package was
created less than one year ago and was deployed to the SSIS catalog. Sometimes the package is started on a SQL
Agent schedule; sometimes the package is started manually by an SSIS developer 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 failed in the past.
Where can you find this information?
A. the SQL Server Log
B. the SSISDB.[catalog].[executions] view
C. the SSISDB.[catalog].[event_messages] view
D. the SQL Agent Job History
E. the SQL Agent Error Log
Correct Answer: B
QUESTION 10
You are developing a SQL Server Integration Services (SSIS) package. The data source for the data flow task is a table
that has been configured as a change data capture (CDC) table. You are using a CDC Source component to obtain the
CDC data.
The CDC Source component has the following requirements:
The output must include metadata columns that indicate which source columns have changed.
The output must return only one change row per source row that is modified in the current CDC processing range.
You need to configure the CDC Source component.
Which CDC processing mode should you select? (To answer, configure the appropriate option or options in the dialog
box in the answer area.)
Hot Area:
QUESTION 11
You are developing a SQL Server Integration Services (SSIS) project by using the Project Deployment Model.
The project is deployed to a single SSIS catalog, and transfers data to and from multiple databases hosted on SQL
Server.
The project must be configured to be able to export data to and from five different production servers that run SQL
Server 2016. Each target server requires different values for connection strings and parameters in the SSIS project.
You need to meet the requirements by using the least amount of administrative effort.
What should you do?
A. For each target server, create separate registry entry configurations. Select the registry entry at package execution
time.
B. For each target server, create separate Environments in the SSIS catalog of the host SQL Server SSIS instance.
Select the appropriate Environment at package execution time.
C. Create one SSIS catalog Environment. Change the values of each Environment variable at package execution time.
D. For each target server, create a separate XML configuration file. Select the XML configuration file at package
execution time.
Correct Answer: B
QUESTION 12
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
Correct Answer: C
QUESTION 13
You have a data warehouse that contains all of the sales data for your company.
You need to design a SQL Server Integration Services (SSIS) package that reports the average value for the one of the
columns in a data flow.
What should you add to the package?
A. Data profiling tasks that have a Column Null profile.
B. Data profiling tasks that have Column value Distribution Profile.
C. Data Profiling tasks that have Column Statistics profile.
D. the Data Mining Query task.
Correct Answer: B
What customers say about Pass4itsure
Latest discount code “2020PASS” – Pass4itsure
Summarize:
[Q1-Q13] Free Microsoft 70-767 dumps pdf download https://drive.google.com/file/d/1NZ9sJeIOEZUW7iu-kvQJWg0PXdPj1WoF/view?usp=sharing
Share all the resources: Latest Microsoft 70-767 practice questions, latest Microsoft 70-767 pdf dumps, Microsoft 70-767 exam video learning. Latest update Microsoft 70-767 dumps https://www.pass4itsure.com/70-767.html Study hard and practices a lot. This will help you prepare for the 70-767 exam. Good luck!
