Introducing The New!
Surepassexam Collection
Get Unlimited Access to all
Surepassexam's PREMIUM files
Q1. You are designing a SQL Server Integration Services (SS1S) package that uploads a file to a table named Orders in a SQL Azure database.
The company's auditing policies have the following requirements:
. An entry must be written to a dedicated SQL Server log table named OrderLog.
. The entry must be written as soon as the file upload task completes.
You need to meet the company's policy requirements. Which event handler should you use?
A. OnProgress
B. Onlnformation
C. OnPostExecute
D. OnComplete
Answer: C
Reference: http://msdn.microsoft.com/en-us/library/ms140223.aspx
Q2. 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:
Q3. 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
Q4. You are developing a SQL Server Integration Services (SSIS) package.
The package control flow will contain many tasks. The tasks will execute consecutively and none will execute more than once. Certain groups of tasks will share variable and transaction scope.
You need to group tasks together while enabling them to be collapsed and expanded as a group.
Which item should you use from the SSIS Toolbox? (To answer, select the appropriate item in the answer area.)
Answer:
Q5. You are designing a SQL Server Integration Services (SSIS) data flow to load sales
transactions from a source system into a data warehouse hosted on Windows Azure SQL Database. One of the columns in the data source is named ProductCode.
Some of the data to be loaded will reference products that need special processing logic in the data flow.
You need to enable separate processing streams for a subset of rows based on the source product code.
Which Data Flow transformation should you use?
A. Script Component
B. Audit
C. Destination Assistant
D. Data Conversion
Answer: A
Q6. You are designing a SQL Server Integration Services (SSIS) data flow to load sales transactions from a source system into a data warehouse hosted on Windows Azure SQL Database. One of the columns in the data source is named ProductCode.
Some of the data to be loaded will reference products that need special processing logic in the data flow.
You need to enable separate processing streams for a subset of rows based on the source product code.
Which data flow transformation should you use?
A. Source Assistant
B. Destination Assistant
C. Audit
D. Script Component
Answer: D
Explanation: Script Component Transformation:
The transformation that uses script to extract, transform, or load data.
Note:
* SQL Server Integration Services provides three different types of data flow components: sources, transformations, and destinations. Sources extract data from data stores such as tables and views in relational databases, files, and Analysis Services databases. Transformations modify, summarize, and clean data. Destinations load data into data stores or create in-memory datasets.
Incorrect: Not A, Not B: No Data Transforms called Source Assistant or Destination Assistant Not C: Not related to auditing
Q7. You are designing a SQL Server Integration Services (SSIS) package that uploads a file to a table named Orders in a SQL Azure database.
The company's auditing policies have the following requirements:
. An entry must be written to a dedicated SQL Server log table named OrderLog. . The entry must be written as soon as the file upload task completes.
You need to meet the company's policy requirements.
Which event handler should you use?
A. OnPostExecute
B. OnWarning
C. OnExecStatusChanged
D. OnVariableValueChanged
Answer: A
Reference: http://msdn.microsoft.com/en-us/library/ms140223.aspx
Q8. You are designing a SQL Server Integration Services (SSIS) package. The package moves order-related data to a staging table named Order. Every night the staging data is truncated and then all the recent orders from the online store database are inserted into the staging table.
Your package must meet the following requirements:
. If the truncate operation fails, the package execution must stop and report an error. . If the Data Flow task that moves the data to the staging table fails, the entire refresh operation must be rolled back. . For auditing purposes, a log entry must be entered in a SQL log table after each execution of the Data Flow task.
The TransactionOption property for the package is set to Required.
You need to design the package to meet the requirements.
How should you design the control flow for the package? (To answer, drag the appropriate setting from the list of settings to the correct location or locations in the answer area.)
Answer:
Q9. You are designing a data warehouse with two fact tables. The first table contains sales per month and the second table contains orders per day.
Referential integrity must be enforced declaratively.
You need to design a solution that can join a single time dimension to both fact tables.
What should you do?
A. Join the two fact tables.
B. Merge the fact tables.
C. Create a time dimension that can join to both fact tables at their respective granularity.
D. Create a surrogate key for the time dimension.
Answer: C
Explanation: References: http://msdn.microsoft.com/en-us/library/ms174537.aspx http://technet.microsoft.com/en-us/library/ms174832.aspx http://msdn.microsoft.com/en-us/library/ms174884.aspx http://decipherinfosys.wordpress.com/2007/02/01/surrogate-keys-vs-natural-keys-for-primary-key/ http://www.agiledata.org/essays/keys.html http://www.databasejournal.com/features/mssql/article.php/3922066/SQL-Server-Natural-Key-Verses-Surrogate-Key.htm http://www.jamesserra.com/archive/2012/01/surrogate-keys/
Q10. 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
Q11. You are designing an enterprise star schema that will consolidate data from three independent data marts. One of the data marts is hosted on SQL Azure.
Most of the dimensions have the same structure and content. However, the geography dimension is slightly different in each data mart.
You need to design a consolidated dimensional structure that will be easy to maintain while ensuring that all dimensional data from the three original solutions is represented.
What should you do?
A. Create a junk dimension for the geography dimension.
B. Implement change data capture.
C. Create a conformed dimension for the geography dimension.
D. Create three geography dimensions.
Answer: C
Q12. You are creating a SQL Server Master Data Services (MDS) model for a company.
The source data for the company is stored in a single table that contains the manager-to-subordinate relationships.
You need to create a hierarchy representing the organizational structure of the company.
Which hierarchy type should you use?
A. Natural
B. Explicit
C. Parent
D. Recursive
Answer: D
Reference: http://technet.microsoft.com/en-us/library/ff487006.aspx
Reference: http://msdn.microsoft.com/en-us/library/ee633747.aspx
Reference: http://technet.microsoft.com/en-us/library/ee633759.aspx
Q13. You are developing a SQL Server Integration Services (SSIS) package.
The package contains several tasks that must repeat until an expression evaluates to
FALSE.
You need to add and configure a container to enable this design.
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:
Q14. You are developing a SQL Server Integration Services (SSIS) package that downloads data from a Windows Azure SQL Database database.
A stored procedure will be called in an Execute SQL task by using an ODBC connection. This stored procedure has only the @CustomerID parameter of type INT.
A project parameter named CustID will be mapped to the stored procedure parameter @CustomerID.
You need to ensure that the value of the CustID parameter is passed to the @CustomerID stored procedure parameter.
In the Parameter Mapping tab of the Execute SQL task editor, how should you configure the parameter? (To answer, drag the appropriate option or options to the correct location or locations in the answer area.)
Answer:
Q15. You manage a SQL Server Master Data Services (MDS) environment.
A new application requires access to the product data that is available in the MDS repository.
You need to design a solution that gives the application access to the product data with the least amount of development effort.
What should you do?
A. Create a Subscription View in MDS.
B. Access the product entity tables in the MDS database directly.
C. Use SQL Server Integration Services (SSIS) to extract the data and put it in a staging database.
D. Use change data capture on the product entity tables.
Answer: A
Q16. You are deploying a new SQL Server Integration Services (SSIS) package to five servers.
The package must meet the following requirements:
. .NET Common Language Runtime (CLR) integration in SQL Server must not be enabled. . The Connection Managers used in the package must be configurable without
editing and redeploying the package.
. The deployment procedure must be automated as much as possible.
. Performance must be maximized.
You need to set up a deployment strategy that meets the requirements.
What should you do?
A. Add an OnError event handler to the SSIS project.
B. Use an msi file to deploy the package on the server.
C. Open a command prompt and run the gacutil command.
D. Open a command prompt and run the dtutil /copy command.
E. Open a command prompt and run the dtexec /rep /conn command.
F. Open a command prompt and run the dtexec /dumperror /conn command.
G. Open a command prompt and execute the package by using the SQL Log provider and running the dtexecui.exe utility.
H. Create a reusable custom logging component and use it in the SSIS project.
I. Configure the SSIS solution to use the Project Deployment Model.
J. Configure the output of a component in the package data flow to use a data tap.
K. Run the dtutil command to deploy the package to the SSIS catalog and store the configuration in SQL Server.
Answer: D