Q1. You are editing a SQL Server Integration Services (SSIS) package that uses checkpoints.
The package performs the following steps:
1. Download a sales transaction file by using FTP.
2. Truncate a staging table.
3. Load the contents of the file to the staging table.
4. Merge the data with another data source for loading to a data warehouse.
The checkpoints are currently working such that if any of the four steps fail, the package will restart from the failed step the next time it executes.
You need to modify the package to ensure that if either the Truncate Staging Table or the Load Sales to Staging task fails, the package will always restart from the Truncate Staging Table task the next time the package runs.
Which three steps 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.)
Answer:
Q2. You are deploying a new SQL Server Integration Services (SSIS) project to the test environment.
A package in the project uses a custom task component.
You need to ensure that the custom object is deployed on the test environment correctly.
What should you do?
A. Run the package by using the dtexec /rep /conn command.
B. Create a reusable custom logging component.
C. Create an OnError event handler.
D. Use the gacutil command.
E. Use the dtutil /copy command.
F. Deploy the package to the Integration Services catalog by using dtutil and use SQL Server to store the configuration.
G. Run the package by using the dtexec /dumperror /conn command.
H. Use the Project Deployment Wizard.
I. Deploy the package by using an msi file.
J. Add a data tap on the output of a component in the package data flow.
K. Run the package by using the dtexecui.exe utility and the SQL Log provider.
Answer: D
Reference:
http://msdn.microsoft.com/en-us/library/ms403356.aspx
Q3. You are designing a SQL Server Integration Services (SSIS) package to execute 12 Transact-SQL (T-SQL) statements on a SQL Azure database. The T-SQL statements may be executed in any order. The T-SQL statements have unpredictable execution times.
You have the following requirements:
. The package must maximize parallel processing of the T-SQL statements.
. After all the T-SQL statements have completed, a Send Mail task must notify administrators.
You need to design the SSIS package. 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.)
Answer:
Q4. You administer a large and complex SQL Server Integration Services (SSIS) solution in the SSIS catalog.
You are instructed to execute a package by using PowerShell. You need to create the correct PowerShell command.
How should you place the four code segments in sequence? (To answer, move the appropriate code segments from the list of code segments to the answer area and arrange them in the correct order.)
Answer:
Q5. 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.
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
Q6. 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?
A. Develop a Master Data Services (MDS) solution.
B. Develop a Data Quality Services (DQS) solution.
C. Create a version control repository for the ETL solution.
D. Develop a custom data lineage solution.
Answer: D
Q7. You are developing a SQL Server Integration Services (SSIS) package that imports unsorted data into a data warehouse hosted on SQL Azure.
You have the following requirements:
. A destination table must contain all of the data in two source tables.
. Duplicate records must be inserted into the destination table.
You need to develop a data flow that imports the data while meeting the requirements.
How should you develop the data flow? (To answer, drag the appropriate transformation from the list of transformations to the correct location in the answer area.)
Answer:
Q8. You are developing a SQL Server Integration Services (SSIS) package. The package contains a user-defined variable named .Queue which has an initial value of 10.
The package control flow contains many tasks that must repeat execution until the .Queue variable equals 0.
You need to enable the tasks to be grouped together for repeat execution.
Which item should you add to the package? (To answer, select the appropriate item in the answer area.)
Answer:
Q9. You are developing a SQL Server Integration Services (SSIS) package. The data source
for the data flow task is a table that has been configured as a change data capture (CDC) table. You are using a CDC Source component to obtain the CDC data.
The CDC Source component has the following requirements:
. The output must include metadata columns that indicate which source columns have changed. . The output must return only one change row per source row that is modified in the current CDC processing range.
You need to configure the CDC Source component.
Which CDC processing mode should you select? (To answer, configure the appropriate option or options in the dialog box in the answer area.)
Answer:
Q10. You plan to deploy a SQL Server Integration Services (SSIS) project by using the project deployment model.
You need to monitor control flow tasks to determine whether any of them are running longer than usual. Which three actions should you perform in sequence? (To answer, move the appropriate actions from the list ofactions to the answer area and arrange them in the correct order.)
Answer: