Introducing The New!
Surepassexam Collection
Get Unlimited Access to all
Surepassexam's PREMIUM files
Q1. You are developing a SQL Server Integration Services (SSIS) package to load data into a data warehouse.
You need to establish the correct order for loading each table to maximize parallel processing.
Which order should you use?
A. 1. DimCurrency, DimScenario, DimAccount in parallel
2. DimOrganization
3. FactFinance
B. 1. DimCurrency, DimOrganization in parallel
2. DimScenario, DimAccount in parallel
3. FactFinance
C. 1. DimCurrency, FactFinance in parallel
2. DimOrganization, DimScenario, DimAccount in parallel
D. 1. FactFinance
2. DimOrganization, DimScenario, DimAccount in parallel
3. DimCurrency
E. 1. DimCurrency
2. DimOrganization
3. DimScenario, DimAccount in parallel
4. FactFinance
Answer: A
Explanation: References: http://msdn.microsoft.com/en-us/library/ms139892.aspx
http://msdn.microsoft.com/en-us/library/ms141261.aspx
Q2. 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:
Q3. You are preparing to install SQL Server 2012 Master Data Services (MDS).
You need to ensure that the database requirements are met.
What should you install?
A. Microsoft SharePoint Foundation 2010 SP1
B. SQL Server 2012 Enterprise (64-bit) x64 on the database server
C. SQL Server 2012 Data Center (64-bit) x64 on the database server
D. SQL Server 2008 Enterprise (64-bit) x64 on the database server
Answer: B
Explanation:
* Master Data Services is a new feature introduced in SQL Server 2008 R2 and further enhanced in SQL Server 2012.
* SQL Server 2012 Enterprise features include Master Data Services:
Note:
* Microsoft SQL Server Master Data Services is a Master Data Management (MDM) product from Microsoft, which will ship as a part of the Microsoft SQL Server database.Originally code-named Bulldog, Master Data Services is the rebranding of the Stratature MDM product titled +EDM, which Microsoft acquired in June 2007. Master Data Services is architecturally similar to +EDM, with increased integration with other Microsoft applications as well as some new features. Master Data Services first shipped with Microsoft SQL Server 2008 R2.
Q4. You are developing a SQL Server Integration Services (SSIS) package to implement an incremental data load strategy. The package reads data from a source system that uses the SQL Server change data capture (CDC) feature.
You have added a CDC Source component to the data flow to read changed data from the source system.
You need to add a data flow transformation to redirect rows for separate processing of insert, update, and delete operations.
Which data flow transformation should you use?
A. Audit
B. DQS Cleansing
C. CDC Splitter
D. Pivot
Answer: D
Q5. 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:
Q6. You are developing a SQL Server Integration Services (SSIS) package to implement an incremental data load strategy. The package reads data from a source system that uses the SQL Server change data capture (CDC) feature.
You have added a CDC Source component to the data flow to read changed data from the source system.
You need to add a data flow transformation to redirect rows for separate processing of insert, update, and delete operations.
Which data flow transformation should you use?
A. Audit
B. Merge Join
C. Merge
D. CDC Splitter
Answer: C
Explanation: MERGE with Incremental Loads The change data capture functionality that is new in SQL Server 2008 makes it easier to perform incremental loads reliably to a data warehouse. As an alternative to using parameterized OLE DB Command transformations to perform the inserts and the updates, you can use the MERGE statement to combine both operations.
Q7. pic 2)
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.
. Support several packages with different configuration settings. 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.
Answer:
Q8. You are developing a project that contains multiple SQL Server Integration Services (SSIS) packages. The packages will be deployed to the SSIS catalog. One of the steps in each package accesses an FTP site to download sales transaction data.
You create project parameters to store the username and password that are used to access the FTP site.
You need to ensure that the username and password values are encrypted when they are deployed.
What should you do?
A. Set the Sensitive property of the parameters to True.
B. Set the ProtectionLevel property of the package to EncryptSensitiveWithUserKey.
C. Change the parameters to package parameters.
D. Change the project to the Legacy Deployment model.
Answer: A
Q9. You administer a Microsoft SQL Server 2012 database. The database contains a table named Employee. Part of the Employee table is shown in the exhibit. (Click the Exhibit button.)
Confidential information about the employees is stored in a separate table named EmployeeData. One record exists within EmployeeData for each record in the Employee table. You need to assign the appropriate constraints and table properties to ensure data integrity and visibility. On which column in the Employee table should you a create a unique constraint?
A. DateHired
B. DepartmentID
C. EmployeelD
D. EmployeeNum
E. FirstName
F. JobTitle
G. LastName
H. MiddleName
I. ReportsToID
Answer: D
Q10. You are designing a SQL Server Integration Services (SSIS) project that uses the Project Deployment Model.
The project will contain 10 packages. All the packages must connect to the same data source and destination.
You need to define and reuse the connection managers in all the packages by using the least development effort.
What should you do?
A. Implement parent package variables.
B. Copy and paste connection managers from one package to the others.
C. Implement project connection managers.
D. Implement package connection managers.
Answer: C
Q11. 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. a Data Conversion component
B. an Execute SQL task that executes a MERGE statement on the database
C. a Merge component
D. an Expression task
Answer: C
Explanation: Note: Type of Slowly Changing Dimensions Slowly Changing Dimensions are categorized into three types named: Type 1, Type 2, and Type3. The Type 1 SCD does not maintain the history of changing attributes, it overwrites values of the attributes. Type 2 maintains historical values for changing attributes. Type 3 that we do not use much maintains separate columns for changed attributes. SSIS SCD wizard supports both Type 1 and Type 2. Using MERGE instead of SCD wizard Replacement of SCD wizard with MERGE is not a straightforward technique. If the SCD has both Type 1 and Type 2 types attributes, they need to be handled separately.
Q12. 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:
Q13. You are the Master Data Services (MDS) administrator at your company.
An existing user needs to integrate data from other systems into MDS.
You need to give the user permissions to integrate data.
Which user management menu item should you select? (To answer, configure the
appropriate option or options in the dialog box in the answer area.)
Answer:
Q14. You are using the Knowledge Discovery feature of the Data Quality Services (DQS) client application to modify an existing knowledge base.
In the mapping configuration, two of the three columns are mapped to existing domains in the knowledge base. The third column, named Group, does not yet have a domain.
You need to complete the mapping of the Group column.
What should you do?
A. Map a composite domain to the source column.
B. Create a composite domain that includes the Group column.
C. Add a domain for the Group column.
D. Add a column mapping for the Group column.
Answer: C
Q15. 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.
Q16. A new dedicated server is used to execute resource-intensive SQL Server Integration Services (SSIS) 2012 packages. The environment that you are deploying the packages to has the following constraints:
The operating system is Windows Server 2008 R2. The SSIS packages are stored in the SSIS catalog. Some of these SSIS packages use 32-bit custom components.
You need to install only the components that are required to deploy and run the packages on the new server.
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: