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 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:
Q2. 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
Q3. You administer a SQL Server Integration Services (SSIS) solution in the SSIS catalog. A SQL Server Agent job is used to execute a package daily with the basic logging level.
Recently, the package execution failed because of a primary key violation when the package inserted data into the destination table.
You need to identify all previous times that the package execution failed because of a primary key violation.
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: E
Q4. You are designing a SQL Server Integration Services (SSIS) 2012 package that imports data from a Windows Azure SQL Database database into a SQL Server database.
The SSIS package has the following requirements:
Every night, a very large amount of data is imported into the staging database.
Package processing time must be minimized.
The package must run on its own dedicated server when it is deployed to
production.
Transaction log activity when data is imported must be minimized.
You need to design the package to meet the requirements.
Which destination component should you use?
A. DataReader
B. OLE DB
C. ADO.NET
D. SQL Server
Answer: B
Q5. You develop a SQL Server Integration Services (SSIS) project by using the Project Deployment model.
The project contains many packages. It is deployed on a server named Development!. The project will be deployed to several servers that run SQL Server 2012.
The project accepts one required parameter. The data type of the parameter is a string.
A SQL Agent job is created that will call the master.dtsx package in the project. A job step is created for the SSIS package.
The job must pass the value of an SSIS Environment Variable to the project parameter. The value of the Environment Variable must be configured differently on each server that runs SQL Server. The value of the Environment Variable must provide the server name to the project parameter.
You need to configure SSIS on the Development1 server to pass the Environment Variable to the package.
Which four actions should you perform in sequence by using SQL Server Management Studio? (To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct order.)
Answer:
Q6. You are developing a SQL Server Integration Services (SSIS) package to load data into a Windows Azure SQL Database database. The package consists of several data flow tasks.
The package has the following auditing requirements:
. If a data flow task fails, a Transact-SQL (T-SQL) script must be executed.
. The T-SQL script must be executed only once per data flow task that fails, regardless of the nature of the error.
You need to ensure that auditing is configured to meet these requirements.
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.va!idate_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 OnTaskFailed event text to the table.
Answer: C
Q7. You are developing a SQL Server Integration Services (SSIS) project to read and write data from a Windows Azure SQL Database database to a server that runs SQL Server 2012.
The connection will be used by data flow tasks in multiple SSIS packages. The address of the target Windows Azure SQL Database database will be provided by a project parameter.
You need to create a solution to meet the requirements by using the least amount of administrative effort and maximizing data flow performance.
What should you do?
A. Use an SSIS Script task that uses the custom assembly to parse the text data when inserting it.
B. Use an SSIS Script transformation that uses the custom assembly to parse the text data when inserting it.
C. Create a SQL Common Language Runtime (SQLCLR) function that uses the custom assembly to parse the text data, deploy it in the Windows Azure SQL Database database, and use it when inserting data.
D. Create a SQL Common Language Runtime (SQLCLR) stored procedure that uses the custom assembly to parse the text data, deploy it in the Windows Azure SQL Database database, and use it when inserting data.
Answer: B
Q8. To ease the debugging of packages, you standardize the SQL Server Integration Services (SSIS) package logging methodology.
The methodology has the following requirements:
Centralized logging in SQL Server
Simple deployment
Availability of log information through reports or T-SQL
Automatic purge of older log entries
Configurable log details
You need to configure a logging methodology that meets the requirements while minimizing the amount of deployment and development effort.
What should you do?
A. Deploy the package by using an msi file.
B. Use the gacutil command.
C. Create an OnError event handler.
D. Create a reusable custom logging component.
E. Use the dtutil /copy command.
F. Use the Project Deployment Wizard.
G. Run the package by using the dtexec /rep /conn command.
H. Add a data tap on the output of a component in the package data flow.
I. Run the package by using the dtexec /dumperror /conn command.
J. Run the package by using the dtexecui.exe utility and the SQL Log provider.
K. Deploy the package to the Integration Services catalog by using dtutil and use SQL Server to store the configuration.
Answer: I
Explanation: References: http://msdn.microsoft.com/en-us/library/ms140246.aspx
http://msdn.microsoft.com/en-us/library/hh231187.aspx
Q9. You are designing a data warehouse that contains a customer dimension.
The customer dimension contains the original customer attributes as well as the most recent set of updated attributes. When a customer is updated, the original customer attributes must be preserved.
The customer dimension design must:
Support a Type 3 Slowly Changing Dimension (SCD) process
Minimize the total storage consumed
You need to design a solution that meets these requirements.
What should you do?
A. Design two tables. One table will hold the original customer surrogate key and attributes. The other table will hold the most recent customer surrogate key and attributes.
B. Design a table as a Type 2 SCD dimension that holds the surrogate key for each customer and its attributes. Limit the number of records per customer within the table to two.
C. Design a table to hold the surrogate key for each customer and its attributes. Add a column to store an indicator that identifies the record as original data or recent data.
D. Design a table to hold the surrogate key for each customer and its attributes as well as additional columns to store the original attributes.
Answer: D
Q10. 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
Q11. You are installing SQL Server Data Quality Services (DQS).
You need to give users belonging to a specific Active Directory group access to the Data Quality Server.
Which SQL Server application should you use?
A. Data Quality Client with administrative credentials
B. SQL Server Configuration Manager with local administrative credentials
C. SQL Server Data Tools with local administrative permissions
D. SQL Server Management Studio with administrative credentials
Answer: D
Q12. You are developing a SQL Server Integration Services (SSIS) project with multiple packages to copy data to a Windows Azure SQL Database database.
An automated process must validate all related Environment references, parameter data types, package references, and referenced assemblies. The automated process must run on a regular schedule.
You need to establish the automated validation process by using the least amount of administrative effort.
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: N
Q13. You are developing a SQL Server Integration Services (SSIS) package that loads data into a data warehouse hosted on Windows Azure SQL Database.
You must combine two data sources together by using the ProductID column to provide complete details for each record. The data retrieved from each data source is sorted in ascending order by the ProductID column.
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:
Q14. You are developing a SQL Server Integration Services (SSIS) project that copies a large amount of rows from a SQL Azure database. The project uses the Package Deployment
Model. This project is deployed to SQL Server on a test server.
You need to ensure that the project is deployed to the SSIS catalog on the production server.
What should you do?
A. Open a command prompt and run the dtexec /dumperror /conn command.
B. Create a reusable custom logging component and use it in the SSIS project.
C. Open a command prompt and run the gacutil command.
D. Add an OnError event handler to the SSIS project.
E. Open a command prompt and execute the package by using the SQL Log provider and running the dtexecui.exe utility.
F. Open a command prompt and run the dtexec /rep /conn command.
G. Open a command prompt and run the dtutil /copy command.
H. Use an msi file to deploy the package on the server.
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: I
Explanation: References: http://msdn.microsoft.com/en-us/library/hh231102.aspx
http://msdn.microsoft.com/en-us/library/hh213290.aspx http://msdn.microsoft.com/en-us/library/hh213373.aspx
Q15. You are developing a SQL Server Integration Services (SSIS) package that imports data into a data warehouse hosted on SQL Azure.
The package uses a Foreach container to process text files found in a folder. The package must be deployed to a single server by using the Project Deployment model.
Multiple SQL Server Agent jobs call the package. Each job is executed on a different schedule.
Each job passes a different folder path to the package.
You need to configure the package to accept the folder path from each job.
Which package configuration should you use?
A. Parent Package Variable
B. XML Configuration File
C. Environment Variable
D. .dtsConfig file
E. Registry Entry
Answer: C
Q16. 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 view on the sales table.
B. Partition the fact tables by day.
C. Create a surrogate key for the time dimension.
D. Change the level of granularity in both fact tables to be the same.
Answer: D
Explanation: In order to join the facts table in time dimension we cannot have two different time measures (time and day).