You are developing a Microsoft SQL Server Integration Services (SSIS) package to incrementally load new and changed records from a data source.
The SSIS package must load new records into Table1 and updated records into Table1_Updates. After loading records, the package must call a Transact-SQL statement to process updated rows according to existing business logic.
You need to complete the design of the SSIS package.
Which tasks should you use? To answer, drag the appropriate SSIS objects to the correct targets. Each SSIS object may be used once, more than once, or not at all. You may need to drag the split bar between panes or scroll to view content.
NOTE: Each correct selection is worth one point.

Explanation

Step 1: CDC Control Task Get Processing Range
Step 2: Mark Processed Range
Step 3: Data Flow
The Data Flow task encapsulates the data flow engine that moves data between sources and destinations, and lets the user transform, clean, and modify data as it is moved. Addition of a Data Flow task to a package control flow makes it possible for the package to extract, transform, and load data.
Step 4: CDC Source
The CDC source reads a range of change data from SQL Server 2017 change tables and delivers the changes downstream to other SSIS component.
Step 5: CDC Splitter
The CDC splitter splits a single flow of change rows from a CDC source data flow into different data flows for Insert, Update and Delete operations.
References:
https://docs.microsoft.com/en-us/sql/integration-services/control-flow/cdc-control-task
https://docs.microsoft.com/en-us/sql/integration-services/control-flow/data-flow-task
https://docs.microsoft.com/en-us/sql/integration-services/data-flow/cdc-splitter?view=sql-server-2017

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 section, you will NOT be able to return to it. As a result, these questions will not appear in the review screen.
Each night you receive a comma separated values (CSV) file that contains different types of rows. Each row type has a different structure. Each row in the CSV file is unique. The first column in every row is named Type. This column identifies the data type.
For each data type, you need to load data from the CSV file to a target table. A separate table must contain the number of rows loaded for each data type.
Solution: You create a SQL Server Integration Services (SSIS) package as shown in the exhibit. (Click the Exhibit tab.)

Does the solution meet the goal?

• A. NO
• B. Yes

Erklärung: (Nur für Fast2test-Mitglieder sichtbar)

You are developing a Microsoft SQL Server Integration Service (SSIS) package. You enable the SSIS log provider for the Windows event log. You configure the package to use the ScriptTaskLogEntry event. You create a custom Script task.
You need to ensure that when the script completes, it writes the execution status to the event log on the server that hosts SSIS.
Which code segment should you add to the Script task?

• A. System.Diagnostics.EventLog.WriteEntry("SSIS", "Script executed with return result" + Dts.TaskResult, System.Diagnostics.EventLogEntryType.Information)
• B. Dts.TaskResult = (int)ScriptResults.Failure
• C. System.Diagnostics.EventLog.WriteEntry("SSIS", "Script executed with return result" + ScriptResults.Success, System.Diagnostics.EventLogEntrytype.Information)
• D. Dts.Events.FireInformation(0, "SSIS", "Script executed with return result" + Dts.TaskResult, String.Empty, 0)

Erklärung: (Nur für Fast2test-Mitglieder sichtbar)

Note: This question is part of a series of questions that use the same scenario. For your convenience, the scenario is repeated in each question. Each question presents a different goal and answer choices, but the text of the scenario is exactly the same in each question in the series.
Start of repeated scenario
You have a Microsoft SQL Server data warehouse instance that supports several client applications.
The data warehouse includes the following tables: Dimension.SalesTerritory, Dimension.Customer, Dimension.Date, Fact.Ticket and Fact.Order. The Dimension.SalesTerritory and Dimension.Customer tables are frequently updated. The Fact.Order table is optimized for weekly reporting, but the company wants to change it to daily. The FactOrder table is loaded by using an ETL process. Indexes have been added to the table over time, but the presence of these indexes slows data loading.
All data in the data warehouse is stored on a shared SAN. All tables are in a database named DB1. You have a second database named DB2 that contains copies of production data for a development environment. The data warehouse has grown and the cost of storage has increased. Data older than one year is accessed infrequently and is considered historical.
* Implement table partitioning to improve the manageability of the data warehouse and to avoid the need to repopulate all transactional data each night Use a partitioning strategy that is as granular as possible.
* Partition the FactOrder table and retain a total of seven years of data.
* Partition the Fact.Ticket table and retain seven years of data. At the end of each month, the partition structure must apply a sliding window strategy to ensure that a new partition is available for the upcoming month, and that the oldest month of data is archived and removed.
* Optimize data loading for the Dimension.SalesTerritory, Dimension.Customer, and Dimension.Date tables.
* Incrementally load all tables in the database and ensure that all incremental changes are processed.
* Maximize the performance during the data loading process for the Fact.Order partition.
* Ensure "that historical data remains online and available for querying.
* Reduce ongoing storage costs while maintaining query performance for current data.
You are not permitted to make changes to the client applications.
End of repeated scenario
You need to optimize data loading for the Dimension.SalesTerritory, Dimension.Customer, and Dimension.Date tables.
Which technology should you use for each table?
To answer, select the appropriate technologies in the answer area.

Explanation

Box 1: Temporal table
Box 2: Temporal table
Compared to CDC, Temporal tables are more efficient in storing historical data as it ignores insert actions.
Box 3: Change Data Capture (CDC)
By using change data capture, you can track changes that have occurred over time to your table. This kind of functionality is useful for applications, like a data warehouse load process that need to identify changes, so they can correctly apply updates to track historical changes over time.
CDC is good for maintaining slowly changing dimensions.
Scenario: Optimize data loading for the Dimension.SalesTerritory, Dimension.Customer, and Dimension.Date tables.
The Dimension.SalesTerritory and Dimension.Customer tables are frequently updated.
References:
https://www.mssqltips.com/sqlservertip/5212/sql-server-temporal-tables-vs-change-data-capture-vs-change-track
https://docs.microsoft.com/en-us/sql/relational-databases/tables/temporal-table-usage-scenarios?view=sql-server

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 section, you will NOT be able to return to it. As a result, these questions will not appear in the review screen.
You plan to deploy a Microsoft SQL server that will host a data warehouse named DB1.
The server will contain four SATA drives configured as a RAID 10 array.
You need to minimize write contention on the transaction log when data is being loaded to the database.
Solution: You configure the server to automatically delete the transaction logs nightly.
Does this meet the goal?

• A. No
• B. Yes

Erklärung: (Nur für Fast2test-Mitglieder sichtbar)