Q1. You are deploying a new SQL Server Integration Services (SSIS) package to several 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 the package.
•The deployment procedure must be automated as much as possible. You need to set up a deployment strategy that meets the requirements. What should you do?
A. Use the gacutil command.
B. Use the dtutil /copy command.
C. Use the Project Deployment Wizard.
D. Create an OnError event handler.
E. Create a reusable custom logging component.
F. Run the package by using the dtexec /rep /conn command.
G. Run the package by using the dtexec /dumperror /conn command.
H. Run the package by using the dtexecui.exe utility and the SQL Log provider.
I. Add a data tap on the output of a component in the package data flow.
J. Deploy the package by using an msi file.
K. Deploy the package to the Integration Services catalog by using dtutil and use SQL Server to store the configuration.
Answer: B
Q2. You are the data steward for a Business Intelligence project.
You must identify duplicate rows stored in a SQL Server table and output discoveries to a CSV file. A Data Quality Services (DQS) knowledge base has been created to support this project.
You need to produce the CSV file with the least amount of development effort. What should you do?
A. Create an Integration Services package and use a Data Profiling transform.
B. Create a custom .NET application based on the Knowledgebase class.
C. Create a data quality project.
D. Create a CLR stored procedure based on the Knowledgebase class.
E. Create a Master Data Services (MDS) business rule.
Answer: C
Explanation:
Reference:
http://msdn.microsoft.com/en-us/library/hh213052.aspx
Q3. DRAG DROP
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:
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. Merge Join
C. Merge
D. CDC Splitter
Answer: D
Explanation: The CDC splitter splits a single flow of change rows from a CDC source data flow into different data flows for Insert, Update and Delete operations
Ref: http://msdn.microsoft.com/en-us/library/hh758656.aspx
Q5. You are reviewing the design of a customer dimension table in an existing data warehouse hosted on SQL Azure.
The current dimension design does not allow the retention of historical changes to customer attributes such as Postcode.
You need to redesign the dimension to enable the full historical reporting of changes to multiple customer attributes including Postcode.
What should you do?
A. Add StartDate and EndDate columns to the customer dimension.
B. Add an IsCurrent column to the customer dimension.
C. Enable Snapshot Isolation on the data warehouse.
D. Add CurrentValue and PreviousValue columns to the customer dimension.
Answer: A
Q6. DRAG DROP
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:
Explanation:
References:
http://msdn.microsoft.com/en-us/library/ms137690.aspx
http://msdn.microsoft.com/en-us/library/ms141144.aspx
Q7. 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.
Answer:
Q8. DRAG DROP
You administer a Microsoft SQL Server database. Service accounts for SQL Agents are configured to use a local user. A Microsoft SQL Server Integration Services (SSIS) job step has been created within a SQL Server Agent job. The SSIS package accesses a network share when exporting data from a SQL Server database.
When you execute the SQL Server Agent job, it fails due to a permissions failure on a share on a remote server. You need to ensure that the SQL Server Agent job can execute the SSIS package. Which four 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:
Explanation:
References:
http://msdn.microsoft.com/en-us/library/ms175834.aspx http://msdn.microsoft.com/en-us/library/ms189522.aspx
http://msdn.microsoft.com/en-us/library/ms190703.aspx http://msdn.microsoft.com/en-us/library/ms161950.aspx
Q9. 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/2021/01/ssis-2021-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
Q10. 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/2021/01/surrogate-keys/