Online Access Free 70-463 Exam Questions

You are designing an extract, transform, load (ETL) process for loading data from a SQL Server database into a large fact table in a data warehouse each day with the prior day's sales data.
The ETL process for the fact table must meet the following requirements:
* Load new data in the shortest possible time.
* Remove data that is more than 36 months old.
* Ensure that data loads correctly.
* Minimize record locking.
* Minimize impact on the transaction log.
You need to design an ETL process that meets the requirements. What should you do? (More than one answer choice may achieve the goal. Select the BEST answer.)

• A.Partition the destination fact table by date. Use partition switching and a staging table to remove old data. Insert new data directly into the fact table.
• B.Partition the destination fact table by customer. Use partition switching both to remove old data and to load new data into each partition.
• C.Partition the destination fact table by date. Insert new data directly into the fact table and delete old data directly from the fact table.
• D.Partition the destination fact table by date. Use partition switching and staging tables both to remove old data and to load new data.

You have a SQL Server Integration Services (SSIS) package that runs on a server named SQL1 and connects to a SQL Server database on a server named SQL2.
You schedule the package to run via a SQL Server Agent job.
The package fails and generates the following error message: "Unknown server or Access is Denied".
You discover that the package is configured to authenticate by using Windows authentication and that the SQL Server Agent on SQL1 uses NT Service\SQLSERVERAGENT as a service account.
You verify that other jobs run on the server without errors.
You create a user account named CONTOSO\JobProcess1 and provide all of the required access to the tables on SQL2.
You need to ensure that the package runs successfully via the SQL Server Agent job.
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.

Correct Answer:

Explanation

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 2012.
Which tool should you use?

• A.SQL Server Configuration Manager
• B.SSIS Upgrade Wizard in SQL Server Management Studio
• C.SQL Server Integration Services Deployment Wizard
• D.SSIS Upgrade Wizard in SQL Server 2008 BIDS

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.an SCD component
• B.a Merge component
• C.a Script component
• D.an Aggregate component

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

Correct Answer:

Explanation