High value of 70 463 dumps simulations materials and ebook for Microsoft certification for IT professionals, Real Success Guaranteed with Updated exam 70 463 pdf dumps vce Materials. 100% PASS Implementing a Data Warehouse with Microsoft SQL Server 2012 exam Today!

Q17. You are developing a SQL Server Integration Services (SSIS) package that imports data into a data warehouse. You add an Execute SQL task to the control flow. 

The task must execute a simple INSERT statement. The task has the following requirements: 

. The INSERT statement must use the value of a string package variable. The variable name is StringVar. . The Execute SQL task must use an OLE DB Connection Manager. 

In the Parameter Mapping tab of the Execute SQL task, StringVar has been added as the only parameter. 

You must configure the SQLStatement property of the Execute SQL task. 

Which SQL statement should you use? 

A. INSERT INTO dbo.Table (variablevalue) VALUES ($StringVar) 

B. INSERT INTO dbo.Table (variablevalue) VALUES (0) 

C. INSERT INTO dbo.Table (variablevalue) VALUES (@0) 

D. INSERT INTO dbo.Table (variablevalue) VALUES (?) 

Answer: D 

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

http://msdn.microsoft.com/en-us/library/ms140355.aspx http://msdn.microsoft.com/en-us/library/cc280502.aspx 


Q18. 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 and ensure that the operations log cleaning requirement is met. 

What should you do? (Each correct answer presents part of the solution. Choose all that apply.) 

A. Enable CLR Integration. 

B. Enable FILESTREAM with Full Access. 

C. Enable the Resource Governor. 

D. Change the recovery mode of the msdb database to FULL. 

E. Change the Server-wide Default Logging Level in SSISDB to Verbose. 

F. Start the SQL Server Browser service. 

G. Start the SQL Server Agent service. 

Answer: A,D 


Q19. You are editing a SQL Server Integration Services (SSIS) project named Project1 in SQL Server Data Tools. 

A package Connection Manager has been parameterized with project scope. 

You need to display the parameters that have been generated for the Connection Manager. 

What should you use? To answer, select the appropriate setting or settings in the answer area. 

Answer: 


Q20. You are creating a SQL Server Integration Services (SSIS) package to populate a fact table from a source table. The fact table and source table are located in a SQL Azure database. The source table has a price field and a tax field. The OLE DB source uses the data access mode of Table. 

You have the following requirements: 

. The fact table must populate a column named TotalCost that computes the sum of the price and tax columns. . Before the sum is calculated, any records that have a price of zero must be discarded. 

You need to create the SSIS package in SQL Server Data Tools. 

In what sequence should you order four of the listed components for the data flow task? (To answer, move the appropriate components from the list of components to the answer area and arrange them in the correct order.) 

Answer: 


Q21. 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. Depending on the value in a source column, the package redirects rows to one of five different data flow paths. 

You need to add a data flow transformation to support the package redirection. 

Which data flow transformation should you use? 

A. Conditional Split 

B. pjvot 

C. Multicast 

D. Lookup 

Answer: A 


Q22. You administer a Microsoft SQL Server 2012 server that has SQL Server Integration Services (SSIS) installed. 

You plan to deploy new SSIS packages to the server. The SSIS packages use the Project Deployment Model together with parameters and Integration Services environment variables. 

You need to configure the SQL Server environment to support these packages. 

What should you do? 

A. Create SSIS configuration files for the packages. 

B. Create an Integration Services catalog. 

C. Install Data Quality Services. 

D. Install Master Data services. 

Answer: B 

Reference: http://msdn.microsoft.com/en-us/library/hh479588.aspx http://msdn.microsoft.com/en-us/library/hh213290.aspx http://msdn.microsoft.com/en-us/library/hh213373.aspx 


Q23. 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 


Q24. 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