Microsoft Implementing a Data Warehouse with Microsoft SQL Server 2012/2014 (070-463) Free Practice Test
Question 1
You are creating a SQL Server Integration Services (SSIS) package to retrieve product data from two different sources. One source is hosted in a SQL Azure database. Each source contains products for different distributors.
Products for each distributor source must be combined for insertion into a single product table destination.
You need to select the appropriate data flow transformation to meet this requirement.
Which transformation types should you use? (Each answer represents a complete solution. Choose all that apply.)
Products for each distributor source must be combined for insertion into a single product table destination.
You need to select the appropriate data flow transformation to meet this requirement.
Which transformation types should you use? (Each answer represents a complete solution. Choose all that apply.)
Correct Answer: B,E
Question 2
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?
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?
Correct Answer: C
Explanation: Only visible for TestSimulate members. You can sign-up / login (it's free).
Question 3
You are developing a SQL Server Integration Services (SSIS) package.
OnError and OnWarning events must be logged for viewing in the built-in SSIS reports by using SQL Server Management Studio.
You need to execute the package and minimize the number of event types that are logged.
Which setting should you use? (To answer, change the appropriate setting in the answer area.)
OnError and OnWarning events must be logged for viewing in the built-in SSIS reports by using SQL Server Management Studio.
You need to execute the package and minimize the number of event types that are logged.
Which setting should you use? (To answer, change the appropriate setting in the answer area.)
Correct Answer:
Explanation
Ref: http://msdn.microsoft.com/en-gb/library/hh231191.aspx
Question 4
You are developing a SQL Server Integration Services (SSIS) project by using the Project Deployment Model.
The project will be deployed to an SSIS catalog folder where Environments have already been created. You need to deploy the project. What should you do?
The project will be deployed to an SSIS catalog folder where Environments have already been created. You need to deploy the project. What should you do?
Correct Answer: F
Question 5
You are designing a package control flow. The package moves sales order data from a SQL Azure transactional database to an on-premise reporting database. The package will run several times a day, while new sales orders are being added to the transactional database. The current design of the package control flow is shown in the answer area. (Click the Exhibit button.)
The Insert New Orders Data Flow task must meet the following requirements:
*Usage of the tempdb database should not be impacted.
*Concurrency should be maximized, while only reading committed transactions.
*If the task fails, only that task needs to be rolled back.
You need to configure the Insert New Orders Data Flow task to meet the requirements.
How should you configure the transaction properties? (To answer, select the appropriate setting or settings in the answer area.)
The Insert New Orders Data Flow task must meet the following requirements:
*Usage of the tempdb database should not be impacted.
*Concurrency should be maximized, while only reading committed transactions.
*If the task fails, only that task needs to be rolled back.
You need to configure the Insert New Orders Data Flow task to meet the requirements.
How should you configure the transaction properties? (To answer, select the appropriate setting or settings in the answer area.)
Correct Answer:
IsolationLevel = ReadCommited.
TransactionOption = Required
References:
http://msdn.microsoft.com/en-us/library/ms137690.aspx
http://msdn.microsoft.com/en-us/library/ms137749.aspx
http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.dts.runtime.dtscontainer.isolationlevel.aspx
http://msdn.microsoft.com/en-us/library/ms173763.aspx
Question 6
NO: 215
Which of the following statements about simple and complex data movements are true? (Choose all that apply.)
Which of the following statements about simple and complex data movements are true? (Choose all that apply.)
Correct Answer: A,C
Explanation: Only visible for TestSimulate members. You can sign-up / login (it's free).
Question 7
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?
You need to ensure that package logging occurs. Your solution must minimize deployment and development efforts.
What should you do?
Correct Answer: K
Explanation: Only visible for TestSimulate members. You can sign-up / login (it's free).
Question 8
You are designing a SQL Server Integration Services (SSIS) solution. The solution will contain an SSIS project that includes several SSIS packages. Each SSIS package will define the same connection managers and variables.
You have the following requirements:
*Ensure that the deployment model supports changing the content of connection strings by using parameters at execution time.
*Ensure that the deployment model automatically starts from calls to the catalog.start_execution stored procedure in the SSISDB database.
*Maximize performance at execution time.
*Minimize development effort.
You need to design a solution that meets the requirements.
What should you do? (More than one answer choice may achieve the goal. Select the BEST answer.)
You have the following requirements:
*Ensure that the deployment model supports changing the content of connection strings by using parameters at execution time.
*Ensure that the deployment model automatically starts from calls to the catalog.start_execution stored procedure in the SSISDB database.
*Maximize performance at execution time.
*Minimize development effort.
You need to design a solution that meets the requirements.
What should you do? (More than one answer choice may achieve the goal. Select the BEST answer.)
Correct Answer: C
Question 9
You develop a SQL Server Integration Services (SSIS) package that imports SQL Azure data into a data warehouse every night.
The SQL Azure data contains many misspellings and variations of abbreviations. To import the data, a developer used the Fuzzy Lookup transformation to choose the closest-matching string from a reference table of allowed values. The number of rows in the reference table is very large.
If no acceptable match is found, the Fuzzy Lookup transformation passes a null value.
The current setting for the Fuzzy Lookup similarity threshold is 0.50.
Many values are incorrectly matched.
You need to ensure that more accurate matches are made by the Fuzzy Lookup transformation without degrading performance.
What should you do?
The SQL Azure data contains many misspellings and variations of abbreviations. To import the data, a developer used the Fuzzy Lookup transformation to choose the closest-matching string from a reference table of allowed values. The number of rows in the reference table is very large.
If no acceptable match is found, the Fuzzy Lookup transformation passes a null value.
The current setting for the Fuzzy Lookup similarity threshold is 0.50.
Many values are incorrectly matched.
You need to ensure that more accurate matches are made by the Fuzzy Lookup transformation without degrading performance.
What should you do?
Correct Answer: A
Explanation: Only visible for TestSimulate members. You can sign-up / login (it's free).
Question 10
You are developing a project that contains multiple SQL Server Integration Services (SSIS) packages. The packages will be deployed to the SSIS catalog. One of the steps in each package accesses an FTP site to download sales transaction data.
You create project parameters to store the username and password that are used to access the FTP site.
You need to ensure that the username and password values are encrypted when they are deployed.
What should you do?
You create project parameters to store the username and password that are used to access the FTP site.
You need to ensure that the username and password values are encrypted when they are deployed.
What should you do?
Correct Answer: C
Question 11
Your team is creating SQL Server Integration Services (SSIS) packages that have several dependencies. The packages use parameters for configuration purposes.
Your company's IT policies include the following change control requirements:
* After a package is ready for deployment, your team must hand over the process to junior IT personnel.
* The process must guarantee that when a package has been validated, that same package (and all its dependencies) is deployed to production.
* The process must be repeatable and reliable and must be executed with the least administrative and training effort by junior IT personnel.
You need to use the most appropriate deployment unit to satisfy the company policies, while minimizing issues such as incorrect version or configuration.
Which type of deployment unit should you use?
Your company's IT policies include the following change control requirements:
* After a package is ready for deployment, your team must hand over the process to junior IT personnel.
* The process must guarantee that when a package has been validated, that same package (and all its dependencies) is deployed to production.
* The process must be repeatable and reliable and must be executed with the least administrative and training effort by junior IT personnel.
You need to use the most appropriate deployment unit to satisfy the company policies, while minimizing issues such as incorrect version or configuration.
Which type of deployment unit should you use?
Correct Answer: B