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. The package consists of several data flow tasks.
The package experiences intermittent errors in the data flow tasks.
If any data flow task fails, all package error information must be captured and written to a SQL Server table by using an OLE DB connection manager.
You need to ensure that the package error information is captured and written to the table.
What should you do?
A. Use an event handler for OnError for the package.
B. Use an event handler for OnError for each data flow task.
C. Use an event handler for OnTaskFailed for the package.
D. View the job history for the SQL Server Agent job.
E. View the All Messages subsection of the All Executions report for the package.
F. Store the System::SourceID variable in the custom log table.
G. Store the System::ServerExecutionID variable in the custom log table.
H. Store the System::ExecutionInstanceGUID variable in the custom log table.
I. Enable the SSIS log provider for SQL Server for OnError in the package control flow.
J. Enable the SSIS log provider for SQL Server for OnTaskFailed in the package control flow.
K. Deploy the project by using dtutil.exe with the /COPY DTS option.
L. Deploy the project by using dtutil.exe with the /COPY SQL option.
M. Deploy the .ispac file by using the Integration Services Deployment Wizard.
N. Create a SQL Server Agent job to execute the SSISDB.catalog.validate_project stored procedure.
O. Create a SQL Server Agent job to execute the SSISDB.catalog.validate_package stored procedure.
P. Create a SQL Server Agent job to execute the
SSISDB.catalog.create_execution and SSISDB.catalog.start_execution stored procedures.
Q. Create a table to store error information. Create an error output on each data flow
destination that writes OnError event text to the table.
R. Create a table to store error information. Create an error output on each data flow
destination that writes OnTaskFailed event text to the table.
Answer: I
Q2. 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. Create a time mapping table.
B. Change the level of granularity in both fact tables to be the same.
C. Merge the fact tables.
D. Create a view on the sales table.
Answer: C
Q3. You are creating a Data Quality Services (DQS) solution. You must provide statistics on the accuracy of the data.
You need to use DQS profiling to obtain the required statistics.
Which DQS activity should you use?
A. Cleansing
B. Knowledge Discovery
C. SQL Profiler
D. Matching Rule Definition
Answer: A
Q4. 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: B,E
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
Q5. You are developing a SQL Server Integration Services (SSIS) package. An Execute SQL
task in the package checks product stock levels and sets a package variable named InStock to TRUE or FALSE depending on the stock level found.
After the successful execution of the Execute SQL task, one of two data flow tasks must run, depending on the value of the InStock variable.
You need to set the precedence constraints.
Which value for the evaluation operation should you use? (To answer, select the appropriate option for the evaluation operation in the answer area.)
Answer:
Q6. 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
Q7. A SQL Server Integration Services (SSIS) package imports daily transactions from several files into a SQL Server table named Transaction. Each file corresponds to a different store and is imported in parallel with the other files. The data flow tasks use OLE DB destinations in fast load data access mode.
The number of daily transactions per store can be very large and is growing- The Transaction table does not have any indexes.
You need to minimize the package execution time.
What should you do?
A. Partition the table by day and store.
B. Create a clustered index on the Transaction table.
C. Run the package in Performance mode.
D. Increase the value of the Row per Batch property.
Answer: D
Explanation: * Data Access Mode – This setting provides the 'fast load' option which internally uses a BULK INSERT statement for uploading data into the destination table instead of a simple INSERT statement (for each single row) as in the case for other options.
* BULK INSERT parameters include: ROWS_PER_BATCH =rows_per_batch Indicates the approximate number of rows of data in the data file. By default, all the data in the data file is sent to the server as a single transaction, and the number of rows in the batch is unknown to the query optimizer. If you specify ROWS_PER_BATCH (with a value > 0) the server uses this value to optimize the bulk-import operation. The value specified for ROWS_PER_BATCH should approximately the same as the actual number of rows.
Q8. 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 conformed dimension for the geography dimension.
B. Implement change tracking.
C. Create a degenerate dimension for the geography dimension.
D. Create a Type 2 slowly changing dimension for the geography dimension.
Answer: A
Q9. You are implementing a SQL Server Integration Services (SSIS) package that imports Microsoft Excel workbook data into a Windows Azure SQL Database database. The package has been deployed to a production server that runs Windows Server 2008 R2 and SQL Server 2012.
The package fails when executed on the production server.
You need to ensure that the package can load the Excel workbook data without errors. You need to use the least amount of administrative effort to achieve this goal.
What should you do?
A. Install a 64-bit ACE driver and execute the package by using the 64-bit run-time option.
B. Enable Address Windowing Extensions (AWE) for the local SQL Server instance.
C. Replace the SSIS Excel source with a SSIS Flat File source.
D. Install a 64-bit ACE driver and replace the Excel source with an OLE DB source.
Answer: D
Explanation: XLSX files, the new file type in Excel 2007/2010 can’t be open with Excel Source/Destination in SSIS. To use XLSX in SSIS we need to install ACE driver and use OLE DB Source/Destination to read read/write data in XLSX files.
ACE is a set of components that facilitate the transfer of data between existing Microsoft Office files such as Microsoft Office Access (*.mdb and *.accdb) files and Microsoft Office Excel 7 (*.xls, *.xlsx, and *.xlsb) files to Microsoft SQL Server. If the SQL Server or your development box does not have ACE driver then install that 1st. Note:
* The most efficient way to link to SQL Server is using an OLEDB connection manager.
* Address Windowing Extensions (AWE) is a Microsoft Windows application programming interface that allows a 32-bit software application to access more physical memory than it has virtual address space.
* Address Windowing Extensions (AWE) is a set of extensions that allows an application to quickly manipulate physical memory greater than 4GB. Certain data-intensive applications, such as database management systems and scientific and engineering software, need access to very large caches of data. In the case of very large data sets, restricting the cache to fit within an application's 2GB of user address space is a severe restriction.
Incorrect: Not B:
Q10. 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 package. After 10 executions, view the emails.
C. Enable logging to the Application Event Log in the package control flow for the OnInformation event. After 10 executions, view the Application Event Log.
D. Enable logging to the Application Event Log in the package control flow for the OnPostExecute event. 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.
Q11. 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
Q12. You are building a SQL Server Integration Services (SSIS) package to load data from all files that are automatically copied to a directory each night through an external FTP process.
You need to load data from all copied files to a destination table in SQL Server.
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:
Q13. You administer a Microsoft SQL Server 2012 database. The database contains a table that has the following definition:
You want to export data from the table to a flat file by using the SQL Server Import and Export Wizard.
You need to ensure that the following requirements are met:
The first row of the file contains the first row of data.
Each record is of the same length.
The date follows the U.S. date format.
The file supports international characters.
What should you do? (To answer, simply select the option or options in the answer area that you would configure.)
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 Team Type, does not yet have a domain.
You need to complete the mapping of the Team Type column.
What should you do?
A. Add a column mapping for the Team Type column.
B. Map a composite domain to the source column.
C. Create a composite domain that includes the Team Type column.
D. Add a domain for the Team Type column.
Answer: D
Explanation: References: http://msdn.microsoft.com/en-us/library/ff877917.aspx
http://msdn.microsoft.com/en-us/library/hh213015.aspx http://msdn.microsoft.com/en-us/library/gg524799.aspx
Q15. 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:
Q16. You are implementing a SQL Server Integration Services (SSIS) 2012 package that loads data from various flat files and a Windows Azure SQL Database database.
Daily transactions must be loaded into a staging database. All the SSIS tasks will use the CurrentDate variable as the transaction date.
You need to set the CurrentDate variable to the date stored in a control table of the Windows Azure SQL Database database when the package starts. You need to achieve this goal by using the least amount of development effort.
What should you use to set the variable?
A. an Expression task
B. an Execute SQL task
C. a Script component
D. a Script task
Answer: B