Q1. You are developing a SQL Server Integration Services (SSIS) package to implement an incremental data load strategy. The package reads data from a source system that uses the SQL Server change data capture (CDC) feature.

You have added a CDC Source component to the data flow to read changed data from the source system.

You need to add a data flow transformation to redirect rows for separate processing of insert, update, and delete operations.

Which data flow transformation should you use?

A. Audit

B. Merge Join

C. Merge

D. CDC Splitter

View Answer

Q2. 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 and ensure that the operations log cleaning requirement is met.

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

A. Enable CLR Integration.

B. Enable FILESTREAM with Full Access.

C. Enable the Resource Governor.

D. Change the recovery mode of the msdb database to FULL.

E. Change the Server-wide Default Logging Level in SSISDB to Verbose.

F. Start the SQL Server Browser service.

G. Start the SQL Server Agent service.

View Answer

Q3. You are developing a SQL Server Integration Services (SSIS) project that copies a large amount of rows from a SQL Azure database. The project uses the Package Deployment Model. This project is deployed to SQL Server on a test server.

You need to ensure that the project is deployed to the SSIS catalog on the production server.

What should you do?

A. Open a command prompt and run the dtexec /dumperror /conn command.

B. Create a reusable custom logging component and use it in the SSIS project.

C. Open a command prompt and run the gacutil command.

D. Add an OnError event handler to the SSIS project.

E. Open a command prompt and execute the package by using the SQL Log provider and running the dtexecui.exe utility.

F. Open a command prompt and run the dtexec /rep /conn command.

G. Open a command prompt and run the dtutil /copy command.

H. Use an msi file to deploy the package on the server.

I. Configure the SSIS solution to use the Project Deployment Model.

J. Configure the output of a component in the package data flow to use a data tap.

K. Run the dtutil command to deploy the package to the SSIS catalog and store the configuration in SQL Server.

View Answer

Q4. HOTSPOT

You are developing a SQL Server Integration Service (SSIS) package. The package loads a customer dimension table by using a data flow task.

Changes to the customer attributes must be tracked over time.

You need to produce a checksum value to identify the rows that have changed since the last Extract, Transform and Load (ETL) process execution. You need to use the least amount of development effort to achieve this goal.

Which transformation should you use? (To answer, select the appropriate transformation in the answer area.)

View Answer

Q5. You are editing a SQL Server Integration Services (SSIS) package that contains three Execute SQL tasks and no other tasks. The package and all three Execute SQL tasks have their TransactionOption property set to Supported.

You need to ensure that if any of the Execute SQL tasks fail, all three tasks will roll back their changes.

What should you do?

A. Move the three Execute SQL tasks into a Sequence container.

B. Move the three Execute SQL tasks into a Foreach Loop container.

C. Change the TransactionOption property of all three Execute SQL tasks to Required.

D. Change the TransactionOption property of the package to Required.

View Answer

Q6. You are editing a SQL Server Integration Services (SSIS) package that contains three Execute SQL tasks and no other tasks. The three Execute SQL tasks modify products in staging tables in preparation for a data warehouse load.

The package and all three Execute SQL product tasks have their TransactionOption property set to Supported.

You need to ensure that if any of the three Execute SQL product tasks fail, all three tasks will roll back their changes.

What should you do?

A. Change the TransactionOption property of the package to Required.

B. Change the TransactionOption property of all three Execute SQL product tasks to Required.

C. Move the three Execute SQL product tasks into a Foreach Loop container.

D. Move the three Execute SQL product tasks into a Sequence container.

View Answer

Q7. DRAG DROP

You are developing a SQL Server Integration Services (SSIS) package that imports data into a data warehouse. You are developing the part of the SSIS package that populates the ProjectDates dimension table.

The business key of the ProjectDates table is the ProjectName column. The business user has given you the dimensional attribute behavior for each of the four columns in the ProjectDates table:

•ExpectedStartDate - New values should be tracked over time.

•ActualStartDate - New values should not be accepted.

•ExpectedEndDate - New values should replace existing values.

•ActualEndDate - New values should be tracked over time.

You use the SSIS Slowly Changing Dimension Transformation.

You must configure the Change Type value for each source column.

Which settings should you select? (To answer, select the appropriate setting or settings in the answer area. Each Change Type may be used once, more than once, or not at all.)

View Answer

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

View Answer

Q9. You are reviewing the design of an existing fact table named factSales, which is loaded incrementally from a SQL Azure database by a SQL Server Integration Services (SSIS) package each hour. The fact table has approximately 4 billion rows and is dimensioned by product, sales date, and sales time of day.

The database administrator is concerned about the rapid growth of the database and users experience poor reporting performance against this database. Reporting requirements have recently changed and the only remaining report that uses this fact table reports sales by product name, sale month, and sale year. No other reports will be created against this table.

You need to reduce the report processing time and minimize the growth of the database. What should you do?

A. Create an indexed view over the fact table to sum orderTotal by month.

B. Create a view over the fact table to sum orderTotal by month.

C. Change the granularity of the fact table to month.

D. Partition the fact table by productKey.

View Answer

Q10. You are installing SQL Server Data Quality Services (DQS).

You need to give specific users access to the Data Quality Server. Which SQL Server application should you use?

A. SQL Server Configuration Manager

B. SQL Server Data Tools

C. SQL Server Management Studio

D. Data Quality Client

View Answer