NEW QUESTION 1
You develop a SQL Server Integration Services (SSIS) project by using the Package Deployment Model. A package in the project extracts data from a Windows Azure SQL Database database. The package is deployed to SQL Server.
The package is not producing the desired results.
You need to generate the .mdmp and .tmp debug files in order to troubleshoot the issues. What should you do?

• A. Execute the catalog.add_data_tap stored procedure with the package execution_id.
• B. Execute the catalog.create_execution_dump stored procedure with the package execution_id.
• C. Run the DTEXEC utility with the /Logger option.
• D. Run the DTEXEC utility with the /Reporting V option.

Answer: C

NEW QUESTION 2
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. SSIS Upgrade Wizard in SQL Server 2008 BIDS
• B. SSIS Upgrade Wizard in SQL Server Data Tools
• C. SQL Server DTExecUI utility (dtexecui.exe)
• D. SQL Server dtexec utility (dtexec.exe)

Answer: B

NEW QUESTION 3
You have a database named Sales and a data warehouse named DataW.
From Sales, you plan to bulk insert to a table in DataW that is more than 2 TB. The process will insert a minimum of 11 million rows simultaneously.
You need to identify which data storage strategy must be used to minimize load times. Which data storage strategy should you identify?

• A. a nonclustered columnstore
• B. FileStream
• C. a durable In-Memory OLTP
• D. non-durable staging table

Answer: D

NEW QUESTION 4
You are installing the Data Quality Client on user desktops.
You need to ensure that the prerequisite software components are installed.
Which components must be present to meet this goal? (Each correct answer presents part of the solution. Choose all that apply.)

• A. SQL Server Management Studio
• B. Internet Explorer 6.0 SP1 or later
• C. Microsoft Silverlight 5
• D. .NET Framework 3.5 SP1
• E. .NET Framework 4.0
• F. Microsoft Silverlight 4
• G. SQL Server Data Tools

Answer: BE

Explanation: Before installing DQS, make sure that your computer meets the minimum system requirements. Data Quality Client requirement:
.NET Framework 4.0 (installed during the Data Quality Client installation, if not already installed) Internet Explorer 6.0 SP1 or later

NEW QUESTION 5
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:

Explanation:

NEW QUESTION 6
You are developing a SQL Server Integration Services (SSIS) package.
To process complex scientific data originating from a SQL Azure database, a custom task component is added to the project.
You need to ensure that the custom component is deployed on a test environment correctly. What should you do?

• A. Add an OnError event handler to the SSIS project.
• B. Open a command prompt and run the gacutil command.
• C. Configure the SSIS solution to use the Project Deployment Model.
• D. Open a command prompt and run the dtexec /dumperror /conn command.
• E. Configure the output of a component in the package data flow to use a data tap.
• F. Open a command prompt and execute the package by using the SQL Log provider and running the dtexecui.exe utility.
• G. Open a command prompt and run the dtexec /rep /conn command.
• H. Run the dtutil command to deploy the package to the SSIS catalog and store the configuration in SQL Server.
• I. Use an msi file to deploy the package on the server.
• J. Open a command prompt and run the dtutil /copy command.
• K. Create a reusable custom logging component and use it in the SSIS project.

Answer: B

Explanation: Reference:
http://msdn.microsoft.com/en-us/library/ms403356.aspx

NEW QUESTION 7
You are designing a data warehouse hosted on Windows Azure SQL Database. The data warehouse currently includes the dimUser and dimRegion dimension tables and the factSales fact table. The dimUser table contains records for each user permitted to run reports against the warehouse, and the dimRegion table contains information about sales regions.
The system is accessed by users from certain regions, as well as by area supervisors and users from the corporate headquarters.
You need to design a table structure to ensure that certain users can see sales data for only certain regions. Some users must be permitted to see sales data from multiple regions.
What should you do?

• A. For each region, create a view of the factSales table that includes a WHERE clause for the region.
• B. Create a userRegion table that contains primary key columns from the dimUser and dimRegion tables.
• C. Add a region column to the dimUser table.
• D. Partition the factSales table on the region colum

Answer: B

NEW QUESTION 8
HOTSPOT
You are developing a SQL Server Integration Services (SSIS) package to load data into a SQL Server 2012 database.
The name of the target instance is passed to the package via an Environment variable.
The Environment variable provides the server name to the Project parameter named ServerName. The OLE DB Project Connection Manager has been parameterized.
You need to configure the Connection Manager property to accept the parameter.
Which property and parameter should you use? (To answer, configure the appropriate option or options in the dialog box in the answer area.)

Answer:

Explanation:

NEW QUESTION 9
DRAG DROP
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:

Explanation: References:
http://msdn.microsoft.com/en-us/library/ms141703.aspx http://msdn.microsoft.com/en-us/library/ms141775.aspx http://msdn.microsoft.com/en-us/library/ms141020.aspx http://msdn.microsoft.com/en-us/library/ms140182.aspx

NEW QUESTION 10
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

Explanation: 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

NEW QUESTION 11
You are a database developer of a Microsoft SQL Server 2012 database. You are designing a table that will store Customer data from different sources. The table will include a column that contains the CustomerID from the source system and a column that contains the SourceID. A sample of this data is as shown in the following table. You need to ensure that the table has no duplicate CustomerID within a SourceID. You also need to ensure that the data in the table is in the order of SourceID and
then CustomerID.
Which Transact- SQL statement should you use?

• A. CREATE TABLE Customer (SourceID int NOT NULL IDENTITY,CustomerID int NOT NULL IDENTITY, CustomerName varchar(255) NOT NULL);
• B. CREATE TABLE Customer (SourceID int NOT NULL,CustomerID int NOT NULL PRIMARY KEY CLUSTERED,CustomerName varchar(255) NOT NULL);
• C. CREATE TABLE Customer(SourceID int NOT NULL PRIMARY KEY CLUSTERED,CustomerID int NOT NULL UNIQUE, CustomerName varchar(255) NOT NULL);
• D. CREATE TABLE Customer (SourceID int NOT NULL, CustomerID int NOT NULL,CustomerName varchar(255) NOT NULL,CONSTRAINT PK_Customer PRIMARY KEY CLUSTERED(SourceID, CustomerID));

Answer: D

NEW QUESTION 12
You are reviewing the design of an existing fact table named factSales, which is loaded incrementally from a SQL Azure database by a SQL Server Integration Services (SSIS) package each hour. The fact table has approximately 4 billion rows and is dimensioned by product, sales date, and sales time of day.

The database administrator is concerned about the rapid growth of the database and users experience poor reporting performance against this database. Reporting requirements have recently changed and the only remaining report that uses this fact table reports sales by product name, sale month, and sale year. No other reports will be created against this table.
You need to reduce the report processing time and minimize the growth of the database. What should you do?

• A. Create an indexed view over the fact table to sum orderTotal by month.
• B. Create a view over the fact table to sum orderTotal by month.
• C. Change the granularity of the fact table to month.
• D. Partition the fact table by productKe

Answer: C

NEW QUESTION 13
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

NEW QUESTION 14
You are installing SQL Server Data Quality Services (DQS).
You need to give specific users access to the Data Quality Server. Which SQL Server application should you use?

• A. SQL Server Configuration Manager
• B. SQL Server Data Tools
• C. SQL Server Management Studio
• D. Data Quality Client

Answer: C

Explanation: Ref: http://msdn.microsoft.com/en-us/library/hh213045.aspx

NEW QUESTION 15
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. Run the DQSInstaller.exe command.
• C. Run the Configuration component in the Data Quality Client.
• D. Make the data available for DQS operation

Answer: B

NEW QUESTION 16
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.NE

Answer: C

Explanation: References:
http://msdn.microsoft.com/en-us/library/ff877917.aspx http://msdn.microsoft.com/en-us/library/gg492277.aspx

NEW QUESTION 17
You develop a SQL Server Integration Services (SSIS) package in a project by using the Project Deployment Model. It is regularly executed within a multi-step SQL Server Agent job.
You make changes to the package that should improve performance.
You need to establish if there is a trend in the durations of the next 10 successful executions of the package. You need to use the least amount of administrative effort to achieve this goal.
What should you do?

• A. After 10 executions, in SQL Server Management Studio, view the Execution Performance subsection of the All Executions report for the package.
• B. Configure the package to send you an email upon completion that includes information about the duration of the packag
• C. After 10 executions, view the emails.
• D. Enable logging to the Application Event Log in the package control flow for the OnInformation even
• E. After 10 executions, view the Application Event Log.
• F. Enable logging to the Application Event Log in the package control flow for the OnPostExecute even
• G. After 10 executions, view the Application Event Log.

Answer: A

Explanation: The All Executions Report displays a summary of all Integration Services executions that have been performed on the server. There can be multiple executions of the sample package. Unlike the Integration Services Dashboard report, you can configure the All Executions report to show executions that have started during a range of dates. The dates can span multiple days, months, or years.
The report displays the following sections of information.
* Filter
Shows the current filter applied to the report, such as the Start time range.
* Execution Information
Shows the start time, end time, and duration for each package execution. You can view a list of the parameter values that were used with a package execution, such as values that were passed to a child package using the Execute Package task.

NEW QUESTION 18
You are designing a SQL Server Integration Services (SSIS) solution that will load multiple Online Transactional Processing (OLTP) data sources into a SQL Server data mart.
You have the following requirements:
• Ensure that the process supports the creation of an exception report that details
possible duplicate key values, null ratios within columns, and column-length distributions of values.
• Ensure that users can generate the exception report in an XML format.
• Use the minimum development effort.
You need to design the SSIS solution to meet the requirements. What should you do? (More than one answer choice may achieve the goal. Select the BEST answer.)

• A. Use a Data Profiling tas
• B. Use a Data Flow task to extract the XML output of the Data Profiling task into a SQL Server tabl
• C. Query the table to view the exceptions.
• D. Use Data Flow tasks to process the clean data.
• E. Use a Data Profiling tas
• F. Read the exceptions in Data Profile Viewer.
• G. Design a stored procedure that examines data for common dirty data pattern
• H. Use an Execute SQL task.

Answer: C