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 is ready for deployment to a production server. The package contains sensitive information secured by using the EncryptSensitiveWithUserKey package protection level.
You are preparing the package for deployment by the production operations team.
You need to ensure that the production operations team can open and execute the package without re-entering the sensitive information.
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:
Q2. You are using SQL Server Data Tools to develop a SQL Server Integration Services (SSIS) project.
The first package that you create in this project contains a package connection that accesses a Microsoft Excel file. Additional packages in the project must also access this file.
You need to define and reuse the Excel file connection in all project packages.
What should you do?
A. Copy the package Connection Manager and paste it in the second package.
B. Set the RetainSameConnection property of the package Connection Manager to True.
C. Convert the package Connection Manager in the first package to a project Connection Manager.
D. Convert the project to the Package Deployment model.
Answer: B
Q3. Occasionally a job that executes an existing SQL Server Integration Services (SSIS) package does not complete and nothing is processed.
You need to ensure that package logging occurs. Your solution must minimize deployment and development efforts.
What should you do?
A. Create a reusable custom logging component.
B. Use the gacutil command.
C. Use the Project Deployment Wizard.
D. Run the package by using the dtexec /rep /conn command.
E. Add a data tap on the output of a component in the package data flow.
F. Create an OnError event handler.
G. Use the dtutil /copy command.
H. Deploy the package by using an msi file.
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: J
Explanation: References: http://msdn.microsoft.com/en-us/library/ms140246.aspx
http://msdn.microsoft.com/en-us/library/hh231187.aspx
Q4. 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
Q5. 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 Fuzzy Lookup transform.
B. Create a Master Data Services (MDS) model.
C. Create a Data Quality Project.
D. Create an Integration Services package and use a DQS Cleansing transform.
E. Create an Integration Services package and use a Fuzzy Grouping transform.
Answer: D
Explanation: The DQS Cleansing transformation uses Data Quality Services (DQS) to correct data from a connected data source, by applying approved rules that were created for the connected data source or a similar data source. To determine whether the data has to be corrected, the DQS Cleansing transformation processes data from an input column when the following conditions are true:
* The column is selected for data correction.
* The column data type is supported for data correction.
* The column is mapped a domain that has a compatible data type.
* The transformation also includes an error output that you configure to handle row-level errors
Q6. To facilitate the troubleshooting of SQL Server Integration Services (SSIS) packages, a logging methodology is put in place.
The methodology has the following requirements:
The deployment process must be simplified.
All the logs must be centralized in SQL Server.
Log data must be available via reports or T-SQL.
Log archival must be automated.
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. Open a command prompt and run the gacutil command.
B. Open a command prompt and execute the package by using the SQL Log provider and running the dtexecui.exe utility.
C. Add an OnError event handler to the SSIS project.
D. Use an msi file to deploy the package on the server.
E. Configure the output of a component in the package data flow to use a data tap.
F. Run the dtutil command to deploy the package to the SSIS catalog and store the configuration in SQL Server.
G. Open a command prompt and run the dtexec /rep /conn command.
H. Open a command prompt and run the dtutil /copy command.
I. Open a command prompt and run the dtexec /dumperror /conn command.
J. Configure the SSIS solution to use the Project Deployment Model.
K. Create a reusable custom logging component and use it in the SSIS project.
Answer: J
Explanation:
References: http://msdn.microsoft.com/en-us/library/ms140246.aspx
http://msdn.microsoft.com/en-us/library/hh231187.aspx
Q7. 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. Multicast
B. Conditional Split
C. Script Task
D. Data Conversion
Answer: C
Q8. 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. DQS Cleansing
B. Merge Join
C. Pivot
D. Conditional Split
Answer: C
Q9. You are creating a SQL Server Integration Services (SSIS) package that implements a Type 3 Slowly Changing Dimension (SCD).
You need to add a task or component to the package that allows you to implement the SCD logic.
What should you use?
A. a Script component
B. an SCD component
C. an Aggregate component
D. a Merge component
Answer: D
Q10. You are performance tuning a SQL Server Integration Services (SSIS) package to load sales data from a source system into a data warehouse that is hosted on Windows Azure SQL Database. The package contains a data flow task that has 10 source-to-destination execution trees.
Only five of the source-to-destination execution trees are running in parallel.
You need to ensure that all the execution trees run in parallel.
What should you do?
A. Add OnError and OnWarning event handlers.
B. Create a new project and add the package to the project.
C. Query the ExecutionLog table.
D. Add an Execute SQL task to the event handlers.
Answer: A
Q11. You are developing a SQL Server Integration Services (SSIS) project that contains a project Connection Manager and multiple packages.
All packages in the project must connect to the same database. The server name for the database must be set by using a parameter named ParamConnection when any package in the project is executed.
You need to develop this project with the least amount of development effort.
What should you do? (Each answer presents a part of the solution. Choose all that apply.)
A. Create a package parameter named ConnectionName in each package.
B. Edit each package Connection Manager. Set the ConnectionName property to @[$Project::ParamConnection].
C. Edit the project Connection Manager in Solution Explorer. Set the ConnectionName property to @ [$Project::ParamConnection].
D. Set the Sensitive property of the parameter to True.
E. Create a project parameter named ConnectionName.
F. Set the Required property of the parameter to True.
Answer: B,E,F
Explanation: B: From question: " The server name for the database must be set by using a parameter named ParamConnection when any package in the project is executed."
E: SSIS 2012 has introduced the concept of Project level connection managers. An SSIS project is generally more than one package. To simplify lives, the SSIS team now allows for the sharing of common resources across projects, connection managers being one of those resources.
F: When a parameter is marked as required, a server value or execution value must be specified for that parameter. Otherwise, the corresponding package does not execute. Although the parameter has a default value at design time, it will never be used once the project is deployed.
Note:
* Integration Services (SSIS) parameters allow you to assign values to properties within packages at the time of package execution. You can create project parameters at the project level and package parameters at the package level. Project parameters are used to supply any external input the project receives to one or more packages in the project. Package parameters allow you to modify package execution without having to edit and redeploy the package.
Reference: Integration Services (SSIS) Parameters
Q12. You are creating a sales data warehouse. When a product exists in the product dimension, you update the product name. When a product does not exist, you insert a new record.
In the current implementation, the DimProduct table must be scanned twice, once for the insert and again for the update. As a result, inserts and updates to the DimProduct table take longer than expected.
You need to create a solution that uses a single command to perform an update and an insert.
How should you use a MERGE T-SQL statement to accomplish this goal? (To answer, drag the appropriate answer choice from the list of options to the correct location or locations in the answer area.
You may need to drag the split bar between panes or scroll to view content.)
Answer:
Q13. You are editing a SQL Server Integration Services (SSIS) package that contains three Execute SQL tasks and no other tasks. The package and all three Execute SQL tasks have their TransactionOption property set to Supported.
You need to ensure that if any of the Execute SQL tasks fail, all three tasks will roll back their changes.
What should you do?
A. Move the three Execute SQL tasks into a Sequence container.
B. Move the three Execute SQL tasks into a Foreach Loop container.
C. Change the TransactionOption property of all three Execute SQL tasks to Required.
D. Change the TransactionOption property of the package to Required.
Answer: D
Reference:
http://msdn.microsoft.com/en-us/library/ms137749.aspx
http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.dts.runtime.dtstransactionoption.aspx
Q14. You administer a Microsoft SQL Server database. Service accounts for SQL Agent 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:
Q15. You are designing a data warehouse for a fresh food distribution business that stores sates by individual product. It stores sales targets by product category. Products are classified into subcategories and categories.
Each product is included in only a single product subcategory, and each subcategory is included in only a single category.
The data warehouse will be a data source for an Analysis Services cube.
The data warehouse contains two fact tables:
. factSales, used to record daily sales by product
. factProductTarget, used to record the monthly sales targets by product category
Reports must be developed against the warehouse that reports product sales by product, category and subcategory, and product sales targets.
You need to design the product dimension. The solution should use as few tables as possible while supporting all the requirements.
What should you do?
A. Create two product tables, dimProduct and dimProductCategory. Connect factSales to dimProduct and factProductTarget to dimProductCategory with foreign key constraints. Direct the cube developer to use key granularity attributes.
B. Create one product table, dimProduct, which contains product detail, category, and subcategory columns. Connect factSales to dimProduct with a foreign key constraint. Direct the cube developer to use a non-key granularity attribute for factProductTarget.
C. Create three product tables, dimProduct, dimProductCategory, and dimProductSubcategory, and a fourth bridge table that joins products to their appropriate category and subcategory table records with foreign key constraints. Direct the cube developer to use key granularity attributes.
D. Create three product tables, dimProduct, dimProductCategory, and dimProductSubcategory. Connect factSales to all three product tables and connect factProductTarget to dimProductCategory with foreign key constraints. Direct the cube developer to use key granularity attributes.
Answer: B
Q16. 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