Q1. You are developing a SQL Server Integration Services (SSIS) package.
OnError and OnWarning events must be logged for viewing in the built-in SSIS reports by
using SQL Server Management Studio.
You need to execute the package and minimize the number of event types that are logged.
Which setting should you use? (To answer, change the appropriate setting in the answer
area.)
Answer:
Q2. 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 Integration Services Deployment Wizard
B. SQL Server Configuration Manager
C. SSIS Upgrade Wizard in SQL Server Management Studio
D. SSIS Upgrade Wizard in SQL Server 2008 BIDS
Answer: C
Explanation: You can upgrade packages that were created in earlier versions of Integration Services to the Integration Services format that SQL Server 2012 uses. SQL Server provides the SSIS Package Upgrade Wizard to help in this process. Because you can configure the wizard to backup up your original packages, you can continue to use the original packages if you experience upgrade difficulties.
You can run the SSIS Package Upgrade Wizard from SQL Server Data Tools (SSDT), from SQL Server Management Studio, or at the command prompt.
Note:
* When you upgrade an instance of SQL Server 2005 or SQL Server 2008 to the current release of SQL Server, your existing SQL Server 2008 Integration Services (SSIS) packages are not automatically upgraded to the package format that the current release SQL Server Integration Services uses. You will have to select an upgrade method and manually upgrade your packages.
Q3. To support the implementation of new reports, Active Directory data will be downloaded to a SQL Server database by using a SQL Server Integration Services (SSIS) 2012 package.
The following requirements must be met:
. All the user information for a given Active Directory group must be downloaded to a SQL Server table. . The download process must traverse the Active Directory hierarchy recursively.
You need to configure the package to meet the requirements by using the least development effort.
Which item should you use?
A. Script task
B. Script component configured as a transformation
C. Script component configured as a source
D. Script component configured as a destination
Answer: C
Q4. 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:
Q5. You are completing the installation of the Data Quality Server component of SQL Server Data Quality Services (DQS).
You need to complete the post-installation configuration.
What should you do?
A. Install the Analysis Services OLE DB Provider.
B. Make the data available for DQS operations.
C. Run the Data Quality Server Installer.
D. Install ADOMD.NET.
Answer: C
Explanation:
References: http://msdn.microsoft.com/en-us/library/ff877917.aspx
http://msdn.microsoft.com/en-us/library/gg492277.aspx
Q6. You are implementing a SQL Server Integration Services (SSIS) package that loads data hosted in a SQL Azure database into a data warehouse.
The source system contains redundant or inconsistent data. When the package finds invalid data, the row containing the invalid data must be omitted but it must also be written to a text file for further analysis.
You need to establish the best technique to log these invalid rows while keeping the amount of development effort to a minimum.
What should you do?
A. Add an OnError event handler to the SSIS project.
B. Open a command prompt and execute the package by using the SQL Log provider and running the dtexecui.exe utility.
C. Use an msi file to deploy the package on the server.
D. Open a command prompt and run the gacutil command.
E. Run the dtutil command to deploy the package to the SSIS catalog and store the configuration in SQL Server.
F. Open a command prompt and run the dtutil /copy command.
G. Create a reusable custom logging component and use it in the SSIS project.
H. Configure the SSIS solution to use the Project Deployment Model.
I. Configure the output of a component in the package data flow to use a data tap.
J. Open a command prompt and run the dtexec /rep /conn command.
K. Open a command prompt and run the dtexec /dumperror /conn command.
Answer: I
Explanation:
References: http://technet.microsoft.com/en-us/library/hh230989.aspx
http://www.rafael-salas.com/2012/01/ssis-2012-quick-peek-to-data-taps.html http://msdn.microsoft.com/en-us/library/ms162820.aspx http://msdn.microsoft.com/en-us/library/hh231187.aspx http://technet.microsoft.com/en-us/library/ms140223.aspx http://msdn.microsoft.com/en-us/library/jj655339.aspx
Q7. You are building a SQL Server Integration Services (SSIS) package to load product data sourced from a SQL Azure database to a data warehouse. Before the product data is loaded, you create a batch record by using an Execute SQL task named Create Batch. After successfully loading the product data, you use another Execute SQL task named Set Batch Success to mark the batch as successful.
You need to create and execute an Execute SQL task to mark the batch as failed if either the Create Batch or Load Products task fails.
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:
Q8. You develop a SQL Server Integration Services (SSIS) package that imports Windows Azure SQL Database data into a data warehouse every night.
The Windows Azure SQL Database data contains many misspellings and variations of abbreviations. To import the data, a developer used the Fuzzy Lookup transformation to choose the closest-matching string from a reference table of allowed values. The number of rows in the reference table is very large.
If no acceptable match is found, the Fuzzy Lookup transformation passes a null value.
The current setting for the Fuzzy Lookup similarity threshold is 0.50.
Many values are incorrectly matched.
You need to ensure that more accurate matches are made by the Fuzzy Lookup transformation without degrading performance.
What should you do?
A. Change the similarity threshold to 0.40.
B. Decrease the maximum number of matches per lookup.
C. Change the similarity threshold to 0.85.
D. Increase the maximum number of matches per lookup.
Answer: C
Explanation: * Similarity threshold Set the similarity threshold at the component level by using the slider. The closer the value is to 1, the closer the resemblance of the lookup value to the source value must be to qualify as a match. Increasing the threshold can improve the speed of matching since fewer candidate records need to be considered.
Incorrect:
* Maximum number of matches to output per lookup
Specify the maximum number of matches the transformation can return for each input row.
The default is 1.
Q9. You are loading a dataset into SQL Server. The dataset contains numerous duplicates for the Artist and Song columns.
The values in the Artist column in the dataset must exactly match the values in the Artist domain in the knowledge base. The values in the Song column in the dataset can be a close match with the values in the Song domain.
You need to use SQL Server Data Quality Services (DQS) to define a matching policy rule to identify duplicates.
How should you configure the Rule Editor? (To answer, drag the appropriate answers to the answer area.)
Answer:
Q10. 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 data source will be polled once per hour. The data is updated with multiple important status changes per minute.
For each captured data change, the before and after values must be included.
You need to configure the CDC Source component.
Which CDC processing mode should you select? (To answer, configure the appropriate option in the dialog box in the answer area.)
Answer: