Implementing a Data Warehouse using SQL
Question No: 21 DRAG DROP – (Topic 1)
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 Development1. The project will be deployed to several servers that run SQL Server 2016.
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.)
http://msdn.microsoft.com/en-us/library/hh479588.aspx http://msdn.microsoft.com/en-us/library/hh213230.aspx http://msdn.microsoft.com/en-us/library/hh213214.aspx http://sqlblog.com/blogs/jamie_thomson/archive/2010/11/13/ssis-server-catalogs- environments-environment-variables-in-ssis-in-denali.aspx
Question No: 22 – (Topic 1)
You are developing a SQL Server Integration Services (SSIS) package that imports data from a relational database to a data warehouse.
You are importing data from a relational table named Projects. The table has change data
capture enabled on all columns.
You need to process only the most recent values from rows that have been inserted or updated since the previous execution of the package.
Which query should you use as the data source?
Question No: 23 – (Topic 1)
To ease the debugging of packages, you standardize the SQL Server Integration Services (SSIS) package logging methodology.
The methodology has the following requirements:
鈥entralized logging in SQL Server
鈥vailability of log information through reports or T-SQL
鈥utomatic purge of older log entries
鈥onfigurable 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?
Deploy the package by using an msi file.
Use the gacutil command.
Create an OnError event handler.
Create a reusable custom logging component.
Use the dtutil /copy command.
Use the Project Deployment Wizard.
Run the package by using the dtexec /rep /conn command.
Add a data tap on the output of a component in the package data flow.
Run the package by using the dtexec /dumperror /conn command.
Run the package by using the dtexecui.exe utility and the SQL Log provider.
Deploy the package to the Integration Services catalog by using dtutil and use SQL Server to store the configuration.
Answer: J Explanation: References:
Question No: 24 – (Topic 1)
You are using a SQL Server Integration Services (SSIS) project that is stored in the SSIS catalog. An Environment has been defined in the SSIS catalog.
You need to add the Environment to the project. Which stored procedure should you use?
Question No: 25 – (Topic 1)
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 Data Conversion component
an Execute SQL task that executes a MERGE statement on the database
a Merge component
an Expression task
Answer: C Explanation:
Note: Type of Slowly Changing Dimensions Slowly Changing Dimensions are categorized into three types named: Type 1, Type 2, and Type3. The Type 1 SCD does not maintain the history of changing attributes, it overwrites values of the attributes. Type 2 maintains historical values for changing attributes. Type 3 that we do not use much maintains separate columns for changed attributes. SSIS SCD wizard supports both Type 1 and Type 2.
Using MERGE instead of SCD wizard Replacement of SCD wizard with MERGE is not a straightforward technique. If the SCD has both Type 1 and Type 2 types attributes, they need to be handled separately.
Question No: 26 – (Topic 1)
You are maintaining a Data Quality Services (DQS) environment. The production server
failed and a new server has been set up. The DQS databases are restored to a new server. All the appropriate permissions are granted.
DQS users are experiencing issues connecting to the new Data Quality Server. You need to enable users to connect to the new server.
Which Surface Area Configuration property should you enable?
Question No: 27 – (Topic 1)
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?
Create a junk dimension for the geography dimension.
Implement change data capture.
Create a conformed dimension for the geography dimension.
Create three geography dimensions.
Question No: 28 – (Topic 1)
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?
Parent Package Variable
XML Configuration File
http://msdn.microsoft.com/en-us/library/ms345179.aspx http://msdn.microsoft.com/en-us/library/ms141708.aspx http://msdn.microsoft.com/en-us/library/hh213214.aspx http://msdn.microsoft.com/en-us/library/hh213296.aspx http://msdn.microsoft.com/en-us/library/hh213293.aspx
Question No: 29 – (Topic 1)
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 flat file. Additional packages in the project must also access this file.
You need to define and reuse the flat file connection in all project packages. What should you do?
Convert the package Connection Manager in the first package to a project Connection Manager.
Copy the package Connection Manager and paste it into the second package.
Convert the project to the Package Deployment model.
Set the ProtectionLevel property of the package Connection Manager to DontSaveSensitive to reuse the flat file connection.
Question No: 30 – (Topic 1)
You install a SQL Server 2016 database engine instance on a production server. A month later, you install SQL Server 2016 Integration Services (SSIS).
You must develop an SSIS project and deploy it to the server by using the Project Deployment model.
Operations Log records that are outside the configured retention period must be cleaned automatically.
You need to create the SSIS catalog on the production server.
What should you do? (Each correct answer presents part of the solution. Choose all that apply.)
Enable XP Command Shell.
Enable CLR Integration.
Enable OLE Automation.
Start the SQL Server Browser service.
Enable Cross Database Ownership Chaining
Start the SQL Server Agent service
Enable Ad Hoc Remote Queries.
|Lowest Price Guarantee||Yes||No||No|
|Free VCE Simulator||Yes||No||No|