Introducing The New!
Surepassexam Collection
Get Unlimited Access to all
Surepassexam's PREMIUM files
Q1. 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 correct answer presents a complete
solution. Choose all that apply.)
A. Multicast
B. Merge Join
C. Term Extraction
D. union All
E. Merge
Answer: D,E
Explanation:
Reference: http://msdn.microsoft.com/en-us/library/ms141703.aspx
Reference: http://msdn.microsoft.com/en-us/library/ms141775.aspx
Reference: http://msdn.microsoft.com/en-us/library/ms141020.aspx
Reference: http://msdn.microsoft.com/en-us/library/ms141809.aspx
Reference: http://msdn.microsoft.com/en-us/library/ms137701.aspx
Q2. HOTSPOT
You are designing a SQL Server Integration Services (SSIS) package configuration strategy.
The package configuration must meet the following requirements:
•Include multiple properties in a configuration.
•Support several packages with different configuration settings.
You need to select the appropriate configuration. Which configuration type should you use? To answer, select the appropriate option from the drop-down list in the dialog box.
Answer:
Q3. DRAG DROP
You plan to deploy a SQL Server Integration Services (SSIS) project by using the project deployment model.
You need to monitor control flow tasks to determine whether any of them are running longer than usual. 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.)
Answer:
Explanation:
1. Connect to SSISDB.
2. Query against execution_component_phases and executions.
3. Execute query
Ref:
http://msdn.microsoft.com/en-us/library/hh230981.aspx http://msdn.microsoft.com/en-us/library/ff878089.aspx
Q4. 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 2021. Which tool should you use?
A. SSIS Upgrade Wizard in SQL Server 2008 BIDS
B. SSIS Upgrade Wizard in SQL Server Data Tools
C. SQL Server DTExecUI utility (dtexecui.exe)
D. SQL Server dtexec utility (dtexec.exe)
Answer: B
Q5. You are the data steward for a Business Intelligence project.
You must identify duplicate rows stored in a SQL Server table and output discoveries to a CSV file. A Data Quality Services (DQS) knowledge base has been created to support this project.
You need to produce the CSV file with the least amount of development effort. What should you do?
A. Create an Integration Services package and use a Fuzzy Lookup transform.
B. Create a Master Data Services (MDS) model.
C. Create a Data Quality Project.
D. Create an Integration Services package and use a DQS Cleansing transform.
E. Create an Integration Services package and use a Fuzzy Grouping transform.
Answer: C
Explanation:
Ref: http://msdn.microsoft.com/en-us/library/hh213052.aspx
Q6. HOTSPOT
You are editing a SQL Server Integration Services (SSIS) project named Project1 in SQL Server Data Tools.
A package Connection Manager has been parameterized with project scope.
You need to display the parameters that have been generated for the Connection Manager.
What should you use? To answer, select the appropriate setting or settings in the answer area.
Answer:
Q7. You are designing a data warehouse with two fact tables. The first table contains sales per month and the second table contains orders per day.
Referential integrity must be enforced declaratively.
You need to design a solution that can join a single time dimension to both fact tables. What should you do?
A. Create a time mapping table.
B. Change the level of granularity in both fact tables to be the same.
C. Merge the fact tables.
D. Create a view on the sales table.
Answer: B
Q8. You are deploying a new SQL Server Integration Services (SSIS) project to the test environment.
A package in the project uses a custom task component.
You need to ensure that the custom object is deployed on the test environment correctly. What should you do?
A. Run the package by using the dtexec /rep /conn command.
B. Create a reusable custom logging component.
C. Create an OnError event handler.
D. Use the gacutil command.
E. Use the dtutil /copy command.
F. Deploy the package to the Integration Services catalog by using dtutil and use SQL Server to store the configuration.
G. Run the package by using the dtexec /dumperror /conn command.
H. Use the Project Deployment Wizard.
I. Deploy the package by using an msi file.
J. Add a data tap on the output of a component in the package data flow.
K. Run the package by using the dtexecui.exe utility and the SQL Log provider.
Answer: D
Explanation:
Reference:
http://msdn.microsoft.com/en-us/library/ms403356.aspx
Q9. HOTSPOT
You are the Master Data Services (MDS) administrator at your company.
An existing user must be denied access to a certain hierarchy node for an existing model. You need to configure the user's permissions.
Which user management menu item should you select? (To answer, configure the appropriate option or options in the dialog box in the answer area.)
Answer:
Q10. 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
Q11. DRAG DROP
You use SQL Server Integration Services (SSIS) for extract, transformation, load (ETL) processing.
Issues concerning addresses are discovered in the data warehouse that you manage. Validation must separate the addresses into three categories:
✑ Valid addresses
✑ Autocorrected addresses
✑ Invalid addresses
You need to enhance the SSIS packages to perform address validation by using an external service.
Which transformation should you use? (To answer, drag the appropriate transformation from the list of options to the correct location or locations in the answer area.)
Answer:
Q12. You are designing a SQL Server Integration Services (SSIS) solution. The solution will contain an SSIS project that includes several SSIS packages. Each SSIS package will define the same connection managers and variables.
You have the following requirements:
•The deployment model must support changing the content of connection strings by using parameters at execution time.
•The deployment model must automatically log events to the SSISDB database.
•Maximize performance at execution time.
You need to design a solution that meets the requirements. What should you do? (More than one answer choice may achieve the goal. Select the BEST answer.)
A. Use a project deployment model. Modify connection manager properties to use project parameters.
B. Use a package deployment model. Save each SSIS package to a file share that can be accessed from all environments.
C. Use a package deployment model. Configure connections in an XML configuration file referenced by an environment variable that corresponds to the SQL Server environment of each SSIS package.
D. Use a project deployment model. Modify connection manager properties to use package parameters.
Answer: A
Q13. HOTSPOT
You are developing a SQL Server Integration Services (SSIS) package. An Execute SQL task in the package checks product stock levels and sets a package variable named InStock to TRUE or FALSE depending on the stock level found.
After the successful execution of the Execute SQL task, one of two data flow tasks must run, depending on the value of the InStock variable.
You need to set the precedence constraints.
Which value for the evaluation operation should you use? (To answer, select the appropriate option for the evaluation operation in the answer area.)
Answer:
Q14. DRAG DROP
You are the administrator for a Data Quality Server. You are adding a user who must have permission to:
✑ Edit and execute a project
✑ View the activity monitoring data
This user must not be able to:
✑ Perform any kind of knowledge management
✑ Create or change a knowledge base
✑ Terminate an activity or perform administrative duties
You need to develop a Transact-SQL (T-SQL) script to meet these requirements.
What should you do? (To answer, drag the appropriate code segment or segments to the correct location or locations in the answer area.)
Answer:
Q15. You administer a Microsoft SQL Server 2021 database. The database contains a table named Employee. Part of the Employee table is shown in the exhibit.
Confidential information about the employees is stored in a separate table named EmployeeData. One record exists within EmployeeData for each record in the Employee table. You need to assign the appropriate constraints and table properties to ensure data integrity and visibility. On which column in the Employee table should you create a unique constraint?
A. DateHired
B. DepartmentID
C. EmployeelD
D. EmployeeNum
E. FirstName
F. JobTitle
G. LastName
H. MiddleName
I. ReportsToID
Answer: D
Q16. You are using a SQL Server Integration Services (SSIS) project that is stored in the SSIS catalog. An Environment has been defined in the SSIS catalog.
You need to add the Environment to the project. Which stored procedure should you use?
A. catalog.set_environment_reference_type
B. catalog.set_environment_property
C. catalog.create_environment_reference
D. catalog.create_environment
Answer: C
Q17. 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