NEW QUESTION 1
You are designing a SQL Server Integration Services (SSIS) package that uses the Fuzzy Lookup transformation.
The reference data to be used in the transformation changes with every package execution. Common language runtime (CLR) integration cannot be enabled on the SQL Server database where the reference table is located.
You need to configure the Fuzzy Lookup transformation in the most efficient manner. What should you do?

• A. Execute the sp_FuzzyLookupTableMaintenanceInvoke stored procedure.
• B. Select the GenerateNewIndex option in the Fuzzy Lookup Transformation Editor.
• C. Execute the sp_FuzzyLookupTableMaintenanceUninstall stored procedure.
• D. Select the GenerateAndPersistNewIndex option in the Fuzzy Lookup Transformation Edito

Answer: B

NEW QUESTION 2
You are developing a SQL Server Integration Services (SSIS) project that contains a project Connection Manager and multiple packages.
All packages in the project must connect to the same database. The server name for the database must be set by using a parameter named ServerParam when any package in the project is executed. You need to develop this project with the least amount of development effort.
What should you do? (Each correct answer presents part of the solution. Choose all that apply.)

• A. Set the Sensitive property of the parameter to True.
• B. Edit each package Connection Manage
• C. Set the ServerName property to@[$Project::ServerParam].
• D. Edit the project Connection Manager in Solution Explore
• E. Set the ServerName property to@[$Project::ServerParam].
• F. Create a project parameter named ServerName.
• G. Create a package parameter named ServerName in each package.
• H. Set the Required property of the parameter to True.

Answer: CDF

Explanation: C: From question: "
The server name for the database must be set by using a parameter named ServerParam when any package in the project is executed."
D: SSIS 2012 has introduced the concept of Project level connection managers. An SSIS project is generally more than one package. To simplify lives, the SSIS team now allows for the sharing of common resources across projects, connection managers being one of those resources.
F: When a parameter is marked as required, a server value or execution value must be specified for that parameter. Otherwise, the corresponding package does not execute. Although the parameter has a default value at design time, it will never be used once the project is deployed.
Note:
* Integration Services (SSIS) parameters allow you to assign values to properties within packages at the time of package execution. You can create project parameters at the project level and package
parameters at the package level. Project parameters are used to supply any external input the project receives to one or more packages in the project. Package parameters allow you to modify package execution without having to edit and redeploy the package.
Reference: Integration Services (SSIS) Parameters

NEW QUESTION 3
DRAG DROP
You deploy a server that has SQL Server installed.
You deploy a SQL Server Integration Services (SSIS) package to the server.
You need to automate the execution of the package. The solution must ensure that you receive a notification if the package fails to execute.
In which order should you perform all the actions? To answer, move all of the actions to the answer area and arrange them in the correct order.
NOTE: More than one order of answer choices is correct. You will receive credit for any of the correct orders you select.

Answer:

Explanation:
Here are the steps: References:
http://www.sherweb.com/blog/how-to-set-up-database-mail-for-sql-server-job-failures/

NEW QUESTION 4
DRAG DROP
You need to design a data load strategy for a data warehouse fact table. The solution must use an ordered data load.
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:

NEW QUESTION 5
DRAG DROP
You are the administrator for a Data Quality Server. You are adding a user who must have permission to:
Edit and execute a project
View the activity monitoring data
This user must not be able to:
Perform any kind of knowledge management
Create or change a knowledge base
Terminate an activity or perform administrative duties
You need to develop a Transact-SQL (T-SQL) script to meet these requirements.
What should you do? (To answer, drag the appropriate code segment or segments to the correct location or locations in the answer area.)

Answer:

Explanation:

NEW QUESTION 6
You are implementing the indexing strategy for a fact table in a data warehouse. The fact table is named Quotes. The table has no indexes and consists of seven columns:
• [ID]
• [QuoteDate]
• [Open]
• [Close]
• [High]
• [Low]
• [Volume]
Each of the following queries must be able to use a columnstore index:
• SELECT AVG ([Close]) AS [AverageClose] FROM Quotes WHERE [QuoteDate] BETWEEN '20100101' AND '20101231'.
• SELECT AVG([High] - [Low]) AS [AverageRange] FROM Quotes WHERE [QuoteDate] BETWEEN '20100101' AND '20101231'.
• SELECT SUM([Volume]) AS [SumVolume] FROM Quotes WHERE [QuoteDate] BETWEEN '20100101' AND '20101231'.
You need to ensure that the indexing strategy meets the requirements. The strategy must also minimize the number and size of the indexes.
What should you do?

• A. Create one columnstore index that contains [ID], [Close], [High], [Low], [Volume], and [QuoteDate].
• B. Create three coiumnstore indexes: One containing [QuoteDate] and [Close]One containing [QuoteDate], [High], and [Low] One containing [QuoteDate] and [Volume]
• C. Create one columnstore index that contains [QuoteDate], [Close], [High], [Low], and [Volume].
• D. Create two columnstore indexes:One containing [ID], [QuoteDate], [Volume], and [Close] One containing [ID], [QuoteDate], [High], and [Low]

Answer: C

Explanation: Reference: http://msdn.microsoft.com/en-us/library/gg492088.aspx Reference: http://msdn.microsoft.com/en-us/library/gg492153.aspx

NEW QUESTION 7
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. DataReader
• B. OLE DB
• C. ADO.NET
• D. SQL Server

Answer: B

NEW QUESTION 8
You are using a SQL Server Integration Services (SSIS) project that is stored in the SSIS catalog. An Environment has been defined in the SSIS catalog.
You need to add the Environment to the project. Which stored procedure should you use?

• A. catalog.create_environment_variable
• B. catalog.create_environment_reference
• C. catalog.set_execution_parameter_value
• D. catalog.set_environment_variable_value

Answer: B

Explanation: Environments (Test, Production etc) are associated with projects by creating references to the environments in the projects.

NEW QUESTION 9
HOTSPOT
You are editing a SQL Server Integration Services (SSIS) package. You need to edit the Script task that has an expression defined.
Which task should you select? To answer, select the appropriate setting or settings in the answer area.

Answer:

Explanation:

NEW QUESTION 10
You are designing a SQL Server Integration Services (SSIS) project that uses the Project Deployment Model.
The project will contain 10 packages. All the packages must connect to the same data source and destination.
You need to define and reuse the connection managers in all the packages by using the least development effort.
What should you do?

• A. Implement parent package variables.
• B. Copy and paste connection managers from one package to the others.
• C. Implement project connection managers.
• D. Implement package connection manager

Answer: C

NEW QUESTION 11
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 that uses the SQL Server change data capture (CDC) feature.
You have added a CDC Source component to the data flow to read changed data from the source system.
You need to add a data flow transformation to redirect rows for separate processing of insert, update, and delete operations.
Which data flow transformation should you use?

• A. Audit
• B. DQS Cleansing
• C. CDC Splitter
• D. Pivot

Answer: C

Explanation: The CDC splitter splits a single flow of change rows from a CDC source data flow into different data flows for Insert, Update and Delete operations
Ref: http://msdn.microsoft.com/en-us/library/hh758656.aspx

NEW QUESTION 12
You are reviewing the design of a student dimension table in an existing data warehouse hosted on SQL Azure.
The current dimension design does not allow the retention of historical changes to student attributes such as ParentOccupation.
You need to redesign the dimension to enable the full historical reporting of changes to multiple student attributes including ParentOccupation.
What should you do?

• A. Add CurrentValue and PreviousValue columns to the student dimension.
• B. Enable Snapshot Isolation on the data warehouse.
• C. Add an IsCurrent column to the student dimension.
• D. Add StartDate and EndDate columns to the student dimension.

Answer: D

Explanation: Adding a start and end date will give you this ability as when a record is inserted and given a start and end date, you’ll have the ability to determine when they were active therefore giving you a retention of historical changes

NEW QUESTION 13
DRAG DROP
You are building a SQL Server Integration Services (SSIS) package to load data from all files that are automatically copied to a directory each night through an external FTP process.
You need to load data from all copied files to a destination table in SQL Server.
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/ms141724.aspx http://msdn.microsoft.com/en-us/library/ms137728.aspx

NEW QUESTION 14
HOTSPOT
You are designing a SQL Server Integration Services (SSIS) package configuration strategy. The package configuration must meet the following requirements:
• Include multiple properties in a configuration.
• Support several packages with different configuration settings.
You need to select the appropriate configuration. Which configuration type should you use? To answer, select the appropriate option from the drop-down list in the dialog box.

Answer:

Explanation:

NEW QUESTION 15
You are designing a SQL Server Integration Services (SS1S) package that uploads a file to a table named Orders in a SQL Azure database.
The company's auditing policies have the following requirements:
• An entry must be written to a dedicated SQL Server log table named OrderLog.
• The entry must be written as soon as the file upload task completes.
You need to meet the company's policy requirements. Which event handler should you use?

• A. OnProgress
• B. Onlnformation
• C. OnPostExecute
• D. OnComplete

Answer: C

Explanation: Reference: http://msdn.microsoft.com/en-us/library/ms140223.aspx

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

• A. Partition the table by day and store.
• B. Create a clustered index on the Transaction table.
• C. Run the package in Performance mode.
• D. Increase the value of the Row per Batch property.

Answer: D

Explanation: * Data Access Mode – This setting provides the 'fast load' option which internally uses a BULK INSERT
statement for uploading data into the destination table instead of a simple INSERT statement (for each single row) as in the case for other options.
* BULK INSERT parameters include: ROWS_PER_BATCH =rows_per_batch
Indicates the approximate number of rows of data in the data file.
By default, all the data in the data file is sent to the server as a single transaction, and the number of rows in the batch is unknown to the query optimizer. If you specify ROWS_PER_BATCH (with a value
> 0) the server uses this value to optimize the bulk-import operation. The value specified for ROWS_PER_BATCH should approximately the same as the actual number of rows.

NEW QUESTION 17
You are using the Knowledge Discovery feature of the Data Quality Services (DQS) client application to modify an existing knowledge base.
In the mapping configuration, two of the three columns are mapped to existing domains in the knowledge base. The third column, named Team Type, does not yet have a domain.
You need to complete the mapping of the Team Type column. What should you do?

• A. Add a column mapping for the Team Type column.
• B. Map a composite domain to the source column.
• C. Create a composite domain that includes the Team Type column.
• D. Add a domain for the Team Type column.

Answer: D

Explanation: References:
http://msdn.microsoft.com/en-us/library/ff877917.aspx http://msdn.microsoft.com/en-us/library/hh213015.aspx http://msdn.microsoft.com/en-us/library/gg524799.aspx

NEW QUESTION 18
DRAG DROP
A SQL Server Integration Services (SSIS) project has been deployed to the SSIS catalog. The project includes a project Connection Manager to connect to the data warehouse.
The SSIS catalog includes two Environments:
Test
Production
Each Environment defines a single Environment Variable named ConnectionString of type string. The value of each variable consists of the connection string to the test or production data warehouses. You need to execute deployed packages by using either of the defined Environments.
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: We need to add references to the Test and Production environments to the project. Then we can map the variables in the project to the environment variables defined in the environments.
When you execute a package in a project that references multiple environments (Test and Production in this case), we can select which environment the package runs under.