Q1. 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.) 

View Answer

Q2. You are adding a new capability to several dozen SQL Server Integration Services (SSIS) packages. 

The new capability is not available as an SSIS task. Each package must be extended with the same new capability. 

You need to add the new capability to all the packages without copying the code between packages. 

What should you do? 

A. Use the Expression task. 

B. Use the Script task. 

C. Develop a custom task. 

D. Use the Script component, 

E. Develop a custom component. 

View Answer

Q3. You are deploying a new SQL Server Integration Services (SSIS) project to the test environment. 

A package in the project uses a custom task component. 

You need to ensure that the custom object is deployed on the test environment correctly. 

What should you do? 

A. Run the package by using the dtexec /rep /conn command. 

B. Create a reusable custom logging component. 

C. Create an OnError event handler. 

D. Use the gacutil command. 

E. Use the dtutil /copy command. 

F. Deploy the package to the Integration Services catalog by using dtutil and use SQL Server to store the configuration. 

G. Run the package by using the dtexec /dumperror /conn command. 

H. Use the Project Deployment Wizard. 

I. Deploy the package by using an msi file. 

J. Add a data tap on the output of a component in the package data flow. 

K. Run the package by using the dtexecui.exe utility and the SQL Log provider. 

View Answer

Q4. You are designing an extract, transform, and load (ETL) solution that loads data into dimension tables. The ETL process involves many transformation steps. 

You need to ensure that the design can provide: 

Auditing information for compliance and business user acceptance Tracking and unique identification of records for troubleshooting and error correction 

What should you do? 

A. Create a SQL Common Language Runtime (SQLCLR) component that records all 

transformation steps in a Microsoft SharePoint list. 

B. Create a version control repository for the transformation steps in Team Foundation Server (TFS). 

C. Develop a custom data lineage solution. 

D. Develop a Data Quality Services (DQS) solution. 

View Answer

Q5. A SQL Server Integration Services (SSIS) package on a computer is failing periodically in production. The package was created less than one year ago and was deployed to the SSIS catalog. 

Sometimes the package is started on a SQL Agent schedule; sometimes the package is started manually by an SSIS developer by using the Object Explorer in SQL Server Management Studio. 

You need to identify the authenticated user responsible for starting the package each time it failed in the past. 

Where can you find this information? 

A. the SQL Server Log 

B. the SSISDB.[catalog].[executions] view 

C. the SSISDB.[catalog].[event_messages] view 

D. the SQL Agent Job History 

E. the SQL Agent Error Log 

View Answer

Q6. 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. Change the level of granularity in both fact tables to be the same. 

C. Merge the fact tables. 

D. Create a view on the sales table. 

View Answer

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

To process complex scientific data originating from a SQL Azure database, a custom task component is added to the project. You need to ensure that the custom component is deployed on a test environment 

correctly. What should you do? 

A. Add an OnError event handler to the SSIS project. 

B. Open a command prompt and run the gacutil command. 

C. Configure the SSIS solution to use the Project Deployment Model. 

D. Open a command prompt and run the dtexec /dumperror /conn command. 

E. Configure the output of a component in the package data flow to use a data tap. 

F. Open a command prompt and execute the package by using the SQL Log provider and running the dtexecui.exe utility. 

G. Open a command prompt and run the dtexec /rep /conn command. 

H. Run the dtutil command to deploy the package to the SSIS catalog and store the configuration in SQL Server. 

I. Use an msi file to deploy the package on the server. 

J. Open a command prompt and run the dtutil /copy command. 

K. Create a reusable custom logging component and use it in the SSIS project. 

View Answer

Q8. You are designing a SQL Server Integration Services (SSIS) 2012 package that imports data from a Windows Azure SQL Database database into a SQL Server database. 

The SSIS package has the following requirements: 

Every night, a very large amount of data is imported into the staging database. 

Package processing time must be minimized. 

The package must run on its own dedicated server when it is deployed to 

production. 

Transaction log activity when data is imported must be minimized. 

You need to design the package to meet the requirements. 

Which destination component should you use? 

A. Raw File 

B. ODBC 

C. Bulk Insert 

D. OLE DB 

View Answer

Q9. You are creating a Data Quality Services (DQS) solution. You must provide statistics on the accuracy of the data. 

You need to use DQS profiling to obtain the required statistics. 

Which DQS activity should you use? 

A. Cleansing 

B. Matching 

C. Knowledge Discovery 

D. Matching Policy 

View Answer

Q10. You are developing a SQL Server Integration Services (SSIS) package to insert new data into a data mart. The package uses a Lookup transformation to find matches between the source and destination. 

The data flow has the following requirements: 

. New rows must be inserted. 

. Lookup failures must be written to a flat file. 

In the Lookup transformation, the setting for rows with no matching entries is set to Redirect rows to no match output. You need to configure the package to direct data into the correct destinations. 

How should you design the data flow outputs? (To answer, drag the appropriate transformation from the list of answer options to the correct location in the answer area.) 

View Answer