70-463 Free Practice Test

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?

1. A. Add a data tap on the output of a component in the package data flow.
2. B. Deploy the package by using an msi file.
3. C. Run the package by using the dtexecui.exe utility and the SQL Log provider.
4. D. uses the dtutil /copy command.
5. E. Deploy the package to the Integration Services catalog by using dtutil and use SQL Server to store the configuration.
6. F. Create an OnError event handler.
7. G. uses the Project Deployment Wizard.
8. H. Use the gacutil command.
9. I. Create a reusable custom logging component.
10. J. Run the package by using the dtexec /rep /conn command.
11. K. Run the package by using the dtexec /dumperror /conn command.

Correct Answer: A
Reference:
http://www.rafael-salas.com/2012/01/ssis-2012-quick-peek-to-data-taps.html http://msdn.microsoft.com/en-us/library/hh230989.aspx http://msdn.microsoft.com/en-us/library/jj655339.aspx

You are designing an Extract, Transform and Load (ETL) solution that loads data into dimension tables. The ETL process involves many transformation steps.
You need to ensure that the design can provide:
Auditing information for compliance and business user acceptance
Tracking and unique identification of records for troubleshooting and error correction What should you do?

1. A. Develop a Master Data Services (MDS) solution.
2. B. Develop a Data Quality Services (DQS) solution.
3. C. Create a version control repository for the ETL solution.
4. D. Develop a custom data lineage solutio

Correct Answer: D

You are designing a SQL Server Integration Services (SSIS) 2012 package that imports data from a Windows Azure SQL Database database into a SQL Server database.
The SSIS package has the following requirements:
Every night, a very large amount of data is imported into the staging database. Package processing time must be minimized.
The package must run on its own dedicated server when it is deployed to production. Transaction log activity when data is imported must be minimized.
You need to design the package to meet the requirements. Which destination component should you use?

1. A. Raw File
2. B. ODBC
3. C. Bulk Insert
4. D. OLE DB

Correct Answer: D

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

1. A. Partition the destination fact table by dat
2. B. Insert new data directly into the fact table and delete old data directly from the fact table.
3. C. Partition the destination fact table by dat
4. D. Use partition switching and staging tables both to remove old data and to load new data.
5. E. Partition the destination fact table by custome
6. F. Use partition switching both to remove old data and to load new data into each partition.
7. G. Partition the destination fact table by dat
8. H. Use partition switching and a staging table to remove old dat
9. I. Insert new data directly into the fact tabl

Correct Answer: B

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.
Force packages to load all settings in the configuration. Support Encrypting File System (EFS) formats.
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.

Solution:


Does this meet the goal?

1. A. Yes
2. B. No

Correct Answer: A