70-767 Exam
Shortcuts To 70-767（41 to 50）
Your success in Microsoft 70-767 is our sole target and we develop all our 70-767 braindumps in a way that facilitates the attainment of this target. Not only is our 70-767 study material the best you can find, it is also the most detailed and the most updated. 70-767 Practice Exams for Microsoft MCSA 70-767 are written to the highest standards of technical accuracy.
Q41. You install a SQL Server 2021 database engine instance on a production server. A month later, you install SQL Server 2021 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: http://msdn.microsoft.com/en-us/library/gg471509(v=sql.110).aspx
Q42. DRAG DROP
You are designing a SQL Server Integration Services (SSIS) package. The package moves order-related data to a staging table named Order. Every night the staging data is truncated and then all the recent orders from the online store database are inserted into the staging table.
Your package must meet the following requirements:
•If the truncate operation fails, the package execution must stop and report an error.
•If the Data Flow task that moves the data to the staging table fails, the entire refresh operation must be rolled back.
•For auditing purposes, a log entry must be entered in a SQL log table after each execution of the Data Flow task.
The TransactionOption property for the package is set to Required. You need to design the package to meet the requirements.
How should you design the control flow for the package? (To answer, drag the appropriate setting from the list of settings to the correct location or locations in the answer area.)
Answer:
Explanation:
References:
http://msdn.microsoft.com/en-us/library/ms137690.aspx
http://msdn.microsoft.com/en-us/library/ms141144.aspx
Q43. HOTSPOT
You are developing a data flow to load sales data into a fact table. In the data flow, you configure a Lookup Transformation in full cache mode to look up the product data for the sale.
The lookup source for the product data is contained in two tables.
You need to set the data source for the lookup to be a query that combines the two tables. Which page of the Lookup Transformation Editor should you select to configure the query?
To answer, select the appropriate page in the answer area.
Answer:
Explanation:
References:
http://msdn.microsoft.com/en-us/library/ms141821.aspx http://msdn.microsoft.com/en-us/library/ms189697.aspx
Q44. You are developing a data flow transformation to merge two data sources. One source contains product data and the other source contains data about the country in which the product was manufactured. Both data sources contain a two-character CountryCode column and both use SQL Server. Both data sources contain an ORDER BY clause to sort the data by the CountryCode column in ascending order.
You use a Merge Join transformation to join the data.
You need to ensure that the Merge Join transformation works correctly without additional transformations. What should you do? (Each answer presents a part of the solution. Choose all that apply.)
A. set the appropriate SortKeyPosition properties on the data sources.
B. set the ValidateExternalMetaData property on the Merge Join transformation to True.
C. set the IsSorted property on both data sources.
D. Set the MaxBuffersPerlnput property on the Merge Join transformation to 2.
Answer: A,C
Explanation: References:
http://msdn.microsoft.com/en-us/library/ms141775.aspx http://msdn.microsoft.com/en-us/library/ms137653.aspx http://siddhumehta.blogspot.com/2009/05/validateexternalmetadata-property.html http://msdn.microsoft.com/en-us/library/ms135950.aspx
Q45. You are developing a SQL Server Integration Services (SSIS) package.
You need to design a package to change a variable value during package execution by using the least amount of development effort.
What should you use?
A. Expression task
B. Data Cleansing transformation
C. Fuzzy Lookup transformation
D. Term Lookup transformation
E. Data Profiling task
Answer: A
Q46. DRAG DROP
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.)
Answer:
Explanation:
1. OLEDB Source
2. Conditional Split
3. Derived Column
4. OLEDB Destination
Q47. HOTSPOT
You are editing a SQL Server Integration Services (SSIS) package. You need to edit the Script task that has an expression defined.
Which task should you select? To answer, select the appropriate setting or settings in the answer area.
Answer:
Q48. DRAG DROP
You administer a Microsoft SQL Server database. Service accounts for SQL Agents are configured to use a local user. A Microsoft SQL Server Integration Services (SSIS) job step has been created within a SQL Server Agent job. The SSIS package accesses a network share when exporting data from a SQL Server database.
When you execute the SQL Server Agent job, it fails due to a permissions failure on a share on a remote server. You need to ensure that the SQL Server Agent job can execute the SSIS package. Which four 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.)
Answer:
Explanation:
References:
http://msdn.microsoft.com/en-us/library/ms175834.aspx http://msdn.microsoft.com/en-us/library/ms189522.aspx
http://msdn.microsoft.com/en-us/library/ms190703.aspx http://msdn.microsoft.com/en-us/library/ms161950.aspx
Q49. DRAG DROP
You are building a SQL Server Integration Services (SSIS) package to load data from all files that are automatically copied to a directory each night through an external FTP process.
You need to load data from all copied files to a destination table in SQL Server.
Which three steps 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.)
Answer:
Explanation:
References:
http://msdn.microsoft.com/en-us/library/ms141724.aspx http://msdn.microsoft.com/en-us/library/ms137728.aspx
Q50. DRAG DROP
A new SQL Server Integration Services (SSIS) project is deployed to the SSIS catalog. To troubleshoot some data issues, you must output the data streaming through several
data flows into text files for further analysis. You have the list of data flow package paths and identification strings of the various task components that must be analyzed.
You need to create these output files with the least amount of administrative and development effort.
Which three stored procedures should you execute in sequence? (To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct order.)
Answer:
Explanation:
Ref: http://msdn.microsoft.com/en-gb/library/hh230989(v=sql.110).aspx