70-463 | Microsoft 70-463 Braindumps 2021

NEW QUESTION 1
To support the implementation of new reports, Active Directory data will be downloaded to a SQL Server database by using a SQL Server Integration Services (SSIS) 2012 package.
The following requirements must be met:
All the user information for a given Active Directory group must be downloaded to a SQL Server table.
The download process must traverse the Active Directory hierarchy recursively.
You need to configure the package to meet the requirements by using the least development effort. Which item should you use?

• A. Script task
• B. Script component configured as a transformation
• C. Script component configured as a source
• D. Script component configured as a destination

Answer: C

NEW QUESTION 2
HOTSPOT
You are developing a SQL Server Integration Services (SSIS) package.
The package must run a parameterized query against a Windows Azure SQL Database database. You need to use the least amount of development effort to meet the package requirement. Which task should you use? (To answer, select the appropriate task in the answer area.)

Answer:

Explanation: Running Parameterized SQL Commands
SQL statements and stored procedures frequently use input parameters, output parameters, and return codes. The Execute SQL task supports the Input, Output, and ReturnValue parameter types. You use the Input type for input parameters, Output for output parameters, and ReturnValue for return codes.
Ref: http://msdn.microsoft.com/en-us/library/ms141003.aspx
In SSIS there are two tasks than can be used to execute SQL statements: Execute T-SQL Statement and Execute SQL. What is the difference between the two?
The Execute T-SQL Statement task tasks less memory, parse time, and CPU time than the Execute SQL task, but is not as filexible. If you need to run parameterized queries, save the query results to
variables, or use property expressions, you should use the Execute SQL task instead of the Execute TSQL Statement task.
Ref: http://www.sqlservercentral.com/blogs/jamesserra/2012/11/08/ssis-execute-sql-task-vsexecute- t-sql-statement-task/

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

NEW QUESTION 4
You have a SQL Server Integration Services (SSIS) package that is configured as shown in the exhibit. (Click the Exhibit button.)

You need to recommend changes to the package to reduce load times.
Which two changes should you recommend? Each correct answer presents part of the solution.

• A. Remove the OLE DB command and use a Script task.
• B. Add an Execute SQL task that batch-updates changed rows.
• C. Set the ValidateExternalMetadata property to False.
• D. Add an Execute SQL task that truncates and reloads the table.
• E. Remove the OLE DB command and use an OLE DB destinatio

Answer: BE

Explanation: If the OLE DB destination uses a fast-load data access mode, you can specify the following fast load options in the user interface, OLE DB Destination Editor, for the destination:
References:
https://docs.microsoft.com/en-us/sql/integration-services/data-flow/ole-db-destination?view=sqlserver- 2021#fast-load-options

NEW QUESTION 5
DRAG DROP
You are developing a SQL Server Integration Services (SSIS) package that imports data into a data
warehouse. You are developing the part of the SSIS package that populates the ProjectDates dimension table.
The business key of the ProjectDates table is the ProjectName column. The business user has given you the dimensional attribute behavior for each of the four columns in the ProjectDates table:
• ExpectedStartDate - New values should be tracked over time.
• ActualStartDate - New values should not be accepted.
• ExpectedEndDate - New values should replace existing values.
• ActualEndDate - New values should be tracked over time. You use the SSIS Slowly Changing Dimension Transformation.
You must configure the Change Type value for each source column.
Which settings should you select? (To answer, select the appropriate setting or settings in the answer area. Each Change Type may be used once, more than once, or not at all.)

Answer:

Explanation: References:
http://msdn.microsoft.com/en-us/library/ms141715.aspx http://msdn.microsoft.com/en-us/library/ms141662.aspx

NEW QUESTION 6
HOTSPOT
You are developing a SQL Server Integration Services (SSIS) package. The data source for the data flow task is a table that has been configured as a change data capture (CDC) table. You are using a CDC Source component to obtain the CDC data.
The CDC Source component has the following requirements:
The output must include metadata columns that indicate which source columns have changed. The output must return only one change row per source row that is modified in the current CDC processing range.
You need to configure the CDC Source component.
Which CDC processing mode should you select? (To answer, configure the appropriate option or options in the dialog box in the answer area.)

Answer:

Explanation: Ref: http://msdn.microsoft.com/en-gb/library/hh231004.aspx

NEW QUESTION 7
You install a SQL Server 2012 database engine instance on a production server. A month later, you install SQL Server 2012 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.)

• A. Enable XP Command Shell.
• B. Enable CLR Integration.
• C. Enable OLE Automation.
• D. Start the SQL Server Browser service.
• E. Enable Cross Database Ownership Chaining
• F. Start the SQL Server Agent service
• G. Enable Ad Hoc Remote Queries.

Answer: BF

Explanation: Ref: http://msdn.microsoft.com/en-us/library/gg471509(v=sql.110).aspx

NEW QUESTION 8
HOTSPOT
You have a data warehouse that is hosted in a SQL Server instance. The data in the data warehouse is loaded by running bcp.exe.
You discover that the transaction logs regularly fill up the local hard disk on the server and that a WRITELOG wait is present.
You need to reduce the amount of disk space used to store the transaction logs and to remove the WRITELOG wait.
Which two database property settings should you modify? To answer, select the appropriate settings in the answer area.

Answer:

Explanation:

NEW QUESTION 9
DRAG DROP
You are building a SQL Server Integration Services (SSIS) package to load product data sourced from a SQL Azure database to a data warehouse. Before the product data is loaded, you create a batch
record by using an Execute SQL task named Create Batch. After successfully loading the product data, you use another Execute SQL task named Set Batch Success to mark the batch as successful.

You need to create and execute an Execute SQL task to mark the batch as failed if either the Create Batch or Load Products task fails.
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:

Explanation: References:
http://msdn.microsoft.com/en-us/library/ms141003.aspx http://msdn.microsoft.com/en-us/library/ms141261.aspx http://msdn.microsoft.com/en-us/library/ms141722.aspx http://msdn.microsoft.com/en-us/library/ms139895.aspx

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

• A. move a table named OLDCUSTOMERS a project Connection Manager.
• B. Copy the package Connection Manager and paste it into the second package.
• C. Convert the project to the Package Deployment model.
• D. Set the ProtectionLevel property of the package Connection Manager to DontSaveSensitive to reuse the flat file connection.

Answer: A

NEW QUESTION 11
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. Destination Assistant
• D. Script Task

Answer: B

Explanation: We use Conditional Split to split the source data into separate processing streams.
A Script Component (Script Component is the answer to another version of this question) could be used but this is not the same as a Script Task.

NEW QUESTION 12
DRAG DROP
You use SQL Server Integration Services (SSIS) for extract, transformation, load (ETL) processing. Issues concerning addresses are discovered in the data warehouse that you manage. Validation must separate the addresses into three categories:
Valid addresses Autocorrected addresses Invalid addresses
You need to enhance the SSIS packages to perform address validation by using an external service. Which transformation should you use? (To answer, drag the appropriate transformation from the list of options to the correct location or locations in the answer area.)

Answer:

Explanation:

NEW QUESTION 13
You are preparing to install SQL Server 2012 Master Data Services (MDS). You need to ensure that the database requirements are met.
What should you install?

• A. Microsoft SharePoint Server 2010 Standard Edition SP1
• B. Microsoft SharePoint Server 2010 Enterprise Edition SP1
• C. SQL Server 2012 Data Center (64-bit) x64 on the database server
• D. SQL Server 2012 Enterprise (64-bit) x64 on the database server

Answer: D

Explanation: * Master Data Services is a new feature introduced in SQL Server 2008 R2 and further enhanced in SQL Server 2012.
* SQL Server 2012 Enterprise features include Master Data Services:

Note:
* Microsoft SQL Server Master Data Services is a Master Data Management (MDM) product from Microsoft, which will ship as a part of the Microsoft SQL Server database.Originally code-named Bulldog, Master Data Services is the rebranding of the Stratature MDM product titled +EDM, which Microsoft acquired in June 2007. Master Data Services is architecturally similar to +EDM, with increased integration with other Microsoft applications as well as some new features. Master Data Services first shipped with Microsoft SQL Server 2008 R2.

NEW QUESTION 14
HOTSPOT
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 areathat you would configure.)

Answer:

Explanation:

NEW QUESTION 15
You are implementing a new 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 current execution date 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. A Script component
• C. A Script task
• D. An Execute SQL task

Answer: A

Explanation: Ref: http://msdn.microsoft.com/en-us/library/ms141214.aspx http://blog.sqltechie.com/2012/11/expression-task-in-ssis-2012.html

NEW QUESTION 16
DRAG DROP
You are developing a SQL Server Integration Services (SSIS) package.
The package contains several tasks that must repeat until an expression evaluates to FALSE. You need to add and configure a container to enable this design.
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:

Explanation: Box 1: Open the Control Flow designer of the package.
Box 2: Add and edit a For Loop container.
Box 3: Configure the EvalExpression property. Note:
* You create the control flow in a package by using the control flow designer.
Integration Services includes three types of containers that you can use in a control flow.
/ Foreach Loop container
/ For Loop container
/ Sequence container
* For Loop Container
The For Loop container defines a repeating control flow in a package. The loop implementation is similar to the For looping structure in programming languages. In each repeat of the loop, the For Loop container evaluates an expression and repeats its workflow until the expression evaluates to False.
The For Loop container uses the following elements to define the loop:
/ An optional initialization expression that assigns values to the loop counters.
/ An evaluation expression that contains the expression used to test whether the loop should stop or continue.
/ An optional iteration expression that increments or decrements the loop counter.

NEW QUESTION 17
HOTSPOT
You are developing a SQL Server Integration Services (SSIS) package. The package contains a userdefined variable named @Queue which has an initial value of 10.
The package control flow contains many tasks that must repeat execution until the @Queue variable equals 0.
You need to enable the tasks to be grouped together for repeat execution.
Which item should you add to the package? (To answer, select the appropriate item in the answer area.)

Answer:

Explanation:

NEW QUESTION 18
In your ETL process, there are three external processes that need to be executed in sequence, but you do not want to stop execution if any of them fails.
Can this be achieved by using precedence constraints? If so, which precedence constraints can be used?

• A. No, this cannot be achieved just by using precedence constraints.
• B. Yes, this can be achieved by using completion precedence constraints between the first and the second and between the second and the third Execute Process tasks, and by using a success precedence constraint between the third Execute Process task and the following task.
• C. Yes, this can be achieved by using completion precedence constraints between the first and the second, between the second and the third, and between the third Execute Process task and the following task.
• D. Yes, this can be achieved by using failure precedence constraints between the first and the second, and between the second and the third Execute Process tasks, and by using a completion precedence constraint between the third Execute Process task and the following task.

Answer: B

Explanation: References:
https://msdn.microsoft.com/en-us/library/cc505848.aspx