NEW QUESTION 1
You are administering SQL Server Integration Services (SSIS) permissions on a production server that runs SQL Server 2012.
Package developers in your company must have permission to perform the following tasks only on their own projects:
View projects and packages, View Environments, Validate packages, Execute packages. You need to grant rights to the developers without assigning unnecessary privileges.
What should you do? (Each correct answer presents part of a solution. Choose all that apply.)

• A. Add developer logins to the db_ssisltduser role in the msdb database.
• B. Add developer logins to the db_ssisoperator role in the msdb database.
• C. Grant Execute permission in the projects for the developer logins.
• D. Grant Read permission in the SSIS catalog folder, the projects, and the Environments.
• E. Add developer logins to the ssis_admin role in the SSISDB database.
• F. Grant Modify permission in the projects for the developer logins.

Answer: BD

Explanation: B: db_ssisoperator
* Read actions Enumerate all packages. View all packages. Execute all packages. Export all packages.
Execute all packages in SQL Server Agent.
* Write actions None
D: Need read permissions on the Environments in order to be able to view them. Incorrect:
Not A: db_ssisltduser
* Read actions Enumerate own packages. Enumerate all packages. View own packages. Execute own packages. Export own packages.
* Write Actions Import packages. Delete own packages.
Change own package roles. Not E: db_ssisadmin
Too many permissions (such as delete all packages).

NEW QUESTION 2
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.)

• A. Slowly Changing Dimension
• B. pivot
• C. Lookup
• D. Union All
• E. Merge

Answer: DE

NEW QUESTION 3
HOTSPOT
You are developing a SQL Server Integration Services (SSIS) package. The data source for the data flow task is a table that has been configured as a change data capture (CDC) table. You are using a CDC Source component to obtain the CDC data.
The data source will be polled once per hour. The data is updated with multiple important status changes per minute.
For each captured data change, the before and after values must be included. You need to configure the CDC Source component.
Which CDC processing mode should you select? (To answer, configure the appropriate option in the dialog box in the answer area.)

Answer:

Explanation:

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

• A. Change the Exhaustive property to True.
• B. Decrease the maximum number of matches per lookup.
• C. Change the similarity threshold to 0.85.
• D. Increase the maximum number of matches per looku

Answer: C

NEW QUESTION 5
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 service

Answer: B

Explanation: 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

NEW QUESTION 6
HOTSPOT
You are developing a SQL Server Integration Services (SSIS) package. The data source for the data flow task is a table that has been configured as a change data capture (CDC) table. You are using a CDC Source component to obtain the CDC data.
The CDC Source component has the following requirements:
The output must include metadata columns that indicate which source columns have changed. The output must return only one change row per source row that is modified in the current CDC processing range.
You need to configure the CDC Source component.
Which CDC processing mode should you select? (To answer, configure the appropriate option or options in the dialog box in the answer area.)

Answer:

Explanation: Ref: http://msdn.microsoft.com/en-gb/library/hh231004.aspx

NEW QUESTION 7
You are developing a SQL Server Integration Services (SSIS) project to read and write data from a Windows Azure SQL Database database to a server that runs SQL Server 2012.
The connection will be used by data flow tasks in multiple SSIS packages. The address of the target Windows Azure SQL Database database will be provided by a project parameter.
You need to create a solution to meet the requirements by using the least amount of administrative effort and maximizing data flow performance.
What should you do?

• A. Use an SSIS Script task that uses the custom assembly to parse the text data when inserting it.
• B. Use an SSIS Script transformation that uses the custom assembly to parse the text data when inserting it.
• C. Create a SQL Common Language Runtime (SQLCLR) function that uses the custom assembly to parse the text data, deploy it in the Windows Azure SQL Database database, and use it when inserting data.
• D. Create a SQL Common Language Runtime (SQLCLR) stored procedure that uses the custom assembly to parse the text data, deploy it in the Windows Azure SQL Database database, and use it when inserting data.

Answer: A

NEW QUESTION 8
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

NEW QUESTION 9
You are editing a SQL Server Integration Services (SSIS) package that contains three Execute SQL tasks and no other tasks. The package and all three Execute SQL tasks have their TransactionOption
property set to Supported.
You need to ensure that if any of the Execute SQL tasks fail, all three tasks will roll back their changes. What should you do?

• A. Move the three Execute SQL tasks into a Sequence container.
• B. Move the three Execute SQL tasks into a Foreach Loop container.
• C. Change the TransactionOption property of all three Execute SQL tasks to Required.
• D. Change the TransactionOption property of the package to Required.

Answer: D

Explanation: Reference:
http://msdn.microsoft.com/en-us/library/ms137749.aspx
http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.dts.runtime.dtstransactionoption.aspx

NEW QUESTION 10
You are developing a SQL Server Integration Services (SSIS) package. The package sources data from an HTML web page that lists product stock levels.
You need to implement a data flow task that reads the product stock levels from the HTML web page. What should you do?

• A. Use an msi file to deploy the package on the server.
• B. Open a command prompt and run the dtutil/copy command.
• C. Add an OnError event handler to the SSIS project.
• D. Open a command prompt and run the gacutil comman

Answer: B

NEW QUESTION 11
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 does not change.
You need to reuse the Fuzzy Lookup match index to increase performance and reduce maintenance. What should you do?

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

Answer: A

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

NEW QUESTION 12
You are the data steward for a Business Intelligence project.
You must identify duplicate rows stored in a SQL Server table and output discoveries to a CSV file. A Data Quality Services (DQS) knowledge base has been created to support this project.
You need to produce the CSV file with the least amount of development effort. What should you do?

• A. Create an Integration Services package and use a Fuzzy Lookup transform.
• B. Create a Master Data Services (MDS) model.
• C. Create a Data Quality Project.
• D. Create an Integration Services package and use a DQS Cleansing transform.
• E. Create an Integration Services package and use a Fuzzy Grouping transform.

Answer: C

Explanation: Ref: http://msdn.microsoft.com/en-us/library/hh213052.aspx

NEW QUESTION 13
HOTSPOT
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.

Answer:

Explanation:

NEW QUESTION 14
You are designing a SQL Server Integration Services (SSIS) 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. OnPostExecute
• B. OnWarning
• C. OnExecStatusChanged
• D. OnVariableValueChanged

Answer: A

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

NEW QUESTION 15
DRAG DROP
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.)

Answer:

Explanation:

NEW QUESTION 16
You maintain a SQL Server Integration Services (SSIS) package. The package was developed by using SQL Server 2008 Business Intelligence Development Studio (BIDS).
The package includes custom scripts that must be upgraded. You need to upgrade the package to SQL Server 2012. Which tool should you use?

• A. SQL Server Configuration Manager
• B. SQL Server Agent
• C. SSIS Upgrade Wizard in SQL Server Management Studio
• D. SQL Server DTExecUI utility (dtexecui.exe)

Answer: C

Explanation: Use the SSIS Package Upgrade Wizard to migrate packages that were developed in SQL Server 2005 Integration Services (SSIS) and SQL Server 2008 Integration Services (SSIS) to the package format that the current release of Integration Services uses.
Reference: SSIS Upgrade Wizard (SSIS Package Upgrade Wizard)

NEW QUESTION 17
You are implementing a new SQL Server Integration Services (SSIS) 2012 package that loads data from various flat files and a Windows Azure SQL Database database.
Daily transactions must be loaded into a staging database. All the SSIS tasks will use the CurrentDate variable as the transaction date.
You need to set the CurrentDate variable to the current execution date when the package starts. You need to achieve this goal by using the least amount of development effort.
What should you use to set the variable?

• A. An Expression task
• B. A Script component
• C. A Script task
• D. An Execute SQL task

Answer: A

Explanation: Ref: http://msdn.microsoft.com/en-us/library/ms141214.aspx http://blog.sqltechie.com/2012/11/expression-task-in-ssis-2012.html

NEW QUESTION 18
You are designing a data warehouse that uses SQL Server 2012.
The data warehouse contains a table named factSales that stores product sales. The table has a clustered index on the primary key, four foreign keys to dimension tables, and an aggregate column for sales totals. All key columns use the int data type and the aggregate column uses the money data type.
You need to increase the speed of data retrieval from the factSales table. Which index type should you add to the table?

• A. Clustered
• B. Semantic search
• C. Nonclustered
• D. XML

Answer: C