Q1. You are developing a SQL Server Integration Services (SSIS) package. 

The package contains an ADO object source variable that holds a result set that was returned by a stored procedure execution. 

You need to add and configure a container that will execute several tasks for each row in the ADO object source variable. 

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,) 

View Answer

Q2. 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 Foundation 2010 SP1 

B. SQL Server 2012 Enterprise (64-bit) x64 on the database server 

C. SQL Server 2012 Data Center (64-bit) x64 on the database server 

D. SQL Server 2008 Enterprise (64-bit) x64 on the database server 

View Answer

Q3. You are completing the installation of the Data Quality Server component of SQL Server Data Quality Services (DQS). 

You need to complete the post-installation configuration. 

What should you do? 

A. Run the Data Quality Server Installer. 

B. Install the data providers that are used for data refresh. 

C. Run the dbimpexp.exe command. 

D. Install the Analysis Services OLE DB Provider. 

View Answer

Q4. You are designing a data warehouse with two fact tables. The first table contains sales per 

month and the second table contains orders per day. 

Referential integrity must be enforced declaratively. 

You need to design a solution that can join a single time dimension to both fact tables. 

What should you do? 

A. Create a time mapping table. 

B. Partition the fact tables by day. 

C. Create a time dimension that can join to both fact tables at their respective granularity. 

D. Join the two fact tables. 

View Answer

Q5. You are developing a SQL Server Integration Services (SSIS) project by using the Project Deployment Model. 

The project is deployed to a single SSIS catalog, and transfers data to and from multiple databases hosted on SQL Server. 

The project must be configured to be able to export data to and from five different production servers that run SQL Server 2012. Each target server requires different values for connection strings and parameters in the SSIS project. 

You need to meet the requirements by using the least amount of administrative effort. 

What should you do? 

A. For each target server, create separate registry entry configurations. Select the registry entry at package execution time. 

B. For each target server, create separate Environments in the SSIS catalog of the host SQL Server SSIS instance. Select the appropriate Environment at package execution time. 

C. Create one SSIS catalog Environment. Change the values of each Environment variable at package execution time. 

D. For each target server, create a separate XML configuration file. Select the XML configuration file at package execution time. 

View Answer

Q6. You are developing a SQL Server Integration Services (SSIS) package. 

The package uses custom functionality that accesses a SQL Server database. The custom 

functionality must be implemented by using Language Integrated Query (LINQ). 

You need to ensure that the LINQ code can be debugged at design time. 

What should you select from the SSIS Toolbox? (To answer, drag the appropriate item to the correct location in the answer area.) 

View Answer

Q7. You administer a Microsoft SQL Server 2012 database. The database contains a table named Employee. Part of the Employee table is shown in the exhibit. (Click the Exhibit button.) 

Confidential information about the employees is stored in a separate table named EmployeeData. One record exists within EmployeeData for each record in the Employee table. You need to assign the appropriate constraints and table properties to ensure data integrity and visibility. On which column in the Employee table should you use an identity specification to include a seed of 1,000 and an increment of 1? 

A. DateHired 

B. DepartmentID 

C. EmployeeID 

D. EmployeeNum 

E. FirstName 

F. JobTitle 

G. LastName 

H. MiddleName 

I. ReportsToID 

View Answer

Q8. A SQL Server Integration Services (SSIS) 2012 package currently downloads sales data from a Windows Azure SQL Database database. 

To improve sales data accuracy, exchange rates must be downloaded daily from a public HTTP website instead of from a weekly flat file. The public website hosts a comma-

separated values (CSV) file that contains one row per currency. 

You need to download the CSV file to the environment. 

What should you use to retrieve the document from the website? 

A. a Script component 

B. a Web Service task 

C. a Web Service source 

D. a Script task 

View Answer

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

View Answer

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

View Answer