Q1. You are maintaining a Data Quality Services (DQS) environment. The production server failed and a new server has been set up. The DQS databases are restored to a new server. All the appropriate permissions are granted.

DQS users are experiencing issues connecting to the new Data Quality Server. You need to enable users to connect to the new server.

Which Surface Area Configuration property should you enable?

A. AdHocRemoteQueriesEnabled

B. SoapEndpointsEnabled

C. ClrIntegrationEnabled

D. RemoteDacEnabled

E. OleAutomationEnabled

F. XpCmdShellEnabled

View Answer

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

View Answer

Q3. You are creating a SQL Server Integration Services (SSIS) package that implements a Type 3 Slowly Changing Dimension (SCD).

You need to add a task or component to the package that allows you to implement the SCD logic.

What should you use?

A. a Script component

B. an SCD component

C. an Aggregate component

D. a Merge component

View Answer

Q4. You are developing a SQL Server Integration Services (SSIS) package to load data into a SQL Server table on ServerA. The package includes a data flow and is executed on ServerB. The destination table has its own identity column.

The destination data load has the following requirements:

✑ The identity values from the source table must be used.

✑ Default constraints on the destination table must be ignored.

✑ Batch size must be 100,000 rows.

You need to add a destination and configure it to meet the requirements. Which destination should you use?

A. OLE DB Destination with Fast Load

B. SQL Server Destination

C. ADO NET Destination without Bulk Insert

D. ADO NET Destination with Bulk Insert

E. OLE DB Destination without Fast Load

View Answer

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

View Answer

Q6. You are writing a SQL Server Integration Services (SSIS) package that transfers data from a legacy system.

Data integrity in the legacy system is very poor. Invalid rows are discarded by the package but must be logged to a CSV file for auditing purposes.

You need to establish the best technique to log these invalid rows while minimizing the amount of development effort.

What should you do?

A. Add a data tap on the output of a component in the package data flow.

B. Deploy the package by using an msi file.

C. Run the package by using the dtexecui.exe utility and the SQL Log provider.

D. uses the dtutil /copy command.

E. Deploy the package to the Integration Services catalog by using dtutil and use SQL Server to store the configuration.

F. Create an OnError event handler.

G. uses the Project Deployment Wizard.

H. Use the gacutil command.

I. Create a reusable custom logging component.

J. Run the package by using the dtexec /rep /conn command.

K. Run the package by using the dtexec /dumperror /conn command.

View Answer

Q7. 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 Data Profiling transform.

B. Create a custom .NET application based on the Knowledgebase class.

C. Create a data quality project.

D. Create a CLR stored procedure based on the Knowledgebase class.

E. Create a Master Data Services (MDS) business rule.

View Answer

Q8. 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. Join the two fact tables.

B. Merge the fact tables.

C. Create a time dimension that can join to both fact tables at their respective granularity.

D. Create a surrogate key for the time dimension.

View Answer

Q9. A SQL Server Integration Services (SSIS) package was deployed two weeks ago with the Project Deployment Model.

Sometimes the package is started as part of a multistep SQL job. At other times, the package is started manually by a database administrator 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 executes.

How can you find this information?

A. In the SSISDB.[catalog], query the .[executions] view.

B. In the SSISDB.[catalog] , query the [event_messages] view.

C. In SQL Server Management Studio, view the SQL Agent Job History.

D. In SQL Server Management Studio, view the SQL Agent Error Log.

E. In SQL Server Management Studio, view the SQL Server Log.

View Answer

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

View Answer