Make your own quiz Manage my Quizzes Plans / Features Guides and Help Contact Privacy & Security

Microsoft 70-463 Practice Questions 2

A visually appealing illustration of a database server with integrated data flow processes, showcasing elements of SQL Server and Integration Services in a professional environment

SQL Server Integration Services (SSIS) Certification Quiz

Test your knowledge and proficiency in SQL Server Integration Services (SSIS) with our comprehensive practice quiz. Designed for database administrators, developers, and anyone involved in data integration tasks, this quiz covers a broad range of topics relevant to SSIS and SQL Server 2012.

Join us and challenge yourself with questions related to:

Package Configurations
Data Flow Transformations
Integration Services Catalog Management
Fuzzy Lookup Methods
Transaction Management

61 Questions15 MinutesCreated by QueryMaster212

Each question is a total of 10 points. There are 60 total questions.
For multichoice questions the points are divided among the correct answers.

It has a limitation in that for multi-choice questions if you choose every answer I think it will give you all the points, even though Microsoft would not allow the same. So you do have to use this tool wisely.

Microsoft has their own practice test which you can purchase as well

Each question is a total of 10 points. There are 60 total questions.
For multichoice questions the points are divided among the correct answers.

Microsoft has their own practice test which you can purchase as well

You work as a database administrator at Lead2pass.com. The Lead2pass.com network consists of a single domain named Lead2pass.com. Lead2pass.com makes use of Microsoft SQL Server 2012 in their environment.
You have recently created an SQL Server Integration Services (SSIS) project catalog that contains two Environments. A single Environment Variable, named ConnectionString, of type string is specified by each environment. The project also has a project Connection Manager configured to link up with the data warehouse.
You have been instructed to make sure that deployed packages are executed using any of the specified Environments.
Which of the following suitably describes an Environment Variable?

An environment variable specifies the data that will be used by a package.

An environment variable can be used to manage Integration Services objects in the catalog.

An environment variable defines a literal value that can be assigned to a parameter during package execution.

An environment variable allows you to use folders to organize your projects and environments.

An environment variable is a container of variables that can be referenced by Integration Services projects.

You work as a senior database developer at Lead2pass.com. The Lead2pass.com network consists of a single domain named Lead2pass.com. Lead2pass.com makes use of Microsoft SQL Server 2012 in their environment.
You are hosting a training exercise for Lead2pass.com’s junior database developers. You are currently discussing an EventHandlerClass property that is used to retrieve or configure a value that indicates whether the container participates in transactions.
Which of the following is the EventHandlerClass property that you are discussing?

The TransactionOption property.

The Executables property.

The LoggingOptions property.

The Variables property.

You work as a senior database administrator at Lead2pass.com. The Lead2pass.com network consists of a single domain named Lead2pass.com. Lead2pass.com makes use of Microsoft SQL Server 2012 in their environment.
You are running a training exercise for Microsoft SQL Server 2012 junior administrators.
You are discussing the effects of setting IsolationLevel transaction property of a Data Flow task to Chaos.
Which of the following suitably describes the effect?

It prevents pending changes from more highly isolated transactions from being overwritten.

It prevents shared locks from being issued and exclusive locks from being honored.

It prevents other users from updating the data.

It reduces blocking by storing a version of data that one application can read while another is modifying the same data.

You work as a database administrator at Lead2pass.com. The Lead2pass.com network consists of a single domain named Lead2pass.com. Lead2pass.com makes use of Microsoft SQL Server 2012 in their environment.
You are in the process of creating a SQL Server Integration Services (SSIS) package. The package will be configured to import data into a SQL Azure hosted data warehouse.
You have decided to make use of the Environment Variable package configuration as part of your solution.
Which of the following is TRUE with regards to the Environment Variable package configuration?

It stores the property path inside the package and the value outside the package.

It stores both the property path and the value outside the package.

It is used to fetch a value from a variable in a calling package.

It is the most commonly used configuration type.

You work as a senior database administrator at Lead2pass.com. The Lead2pass.com network consists of a single domain named Lead2pass.com. Lead2pass.com makes use of Microsoft SQL Server 2012 in their environment.
You are hosting a training exercise for Microsoft SQL Server 2012 administrators.
You are currently discussing the Fuzzy Lookup transformation.
Which of the following is TRUE with regards to the Fuzzy Lookup similarity threshold setting? (Choose all that apply.)

Increasing the threshold can improve the speed of matching.

Increasing the threshold can deteriorate the speed of matching.

The nearer the value is to 1, the nearer the resemblance of the lookup value to the source value must be to qualify as a match.

The nearer the value is to 1, the further the resemblance of the lookup value to the source value must be to qualify as a match.

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?

Create SSIS configuration files for the packages.

Create an Integration Services catalog.

Install Data Quality Services.

Install Master Data services.

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 SQL Azure. 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?

Source Assistant

Audit

Destination Assistant

Script Component

You work as a senior database administrator at ABC.com. The ABC.com network consists of a single domain named ABC.com.
ABC.com makes use of Microsoft SQL Server 2012 in their environment.
You are running a training exercise for Microsoft SQL Server 2012 junior administrators.
You are discussing the use of Slowly Changing Dimension Transformation Outputs. One of the output options causes Derived Column transformations to create columns for the expired row and the current row indicators.
Which option is the output that causes this?

Unchanged Output

Inferred Member Updates Output

Historical Attributes Inserts Output

Fixed Attribute Output

Changing Attributes Updates Output

You work as a senior database developer at Lead2pass.com. The Lead2pass.com network consists of a single domain named Lead2pass.com. Lead2pass.com makes use of Microsoft SQL Server 2012 in their environment.
You are running a training exercise for Microsoft SQL Server 2012 junior developers.
You are discussing the ValidateExternalMetaData property for a data flow.
Which of the following represents the data type for this property?

Integer

IDTSExternalMetadataColumn100

String

Boolean

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?

The SQL Server Log

The SSISDB.[catalog].[executions] view

The SSISDB.[catalog].[event_messages] view

The SQL Agent Job History

The SQL Agent Error Log

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?

SSIS Upgrade Wizard in SQL Server 2008 BIDS

SSIS Upgrade Wizard in SQL Server Data Tools

SQL Server DTExecUI utility (dtexecui.exe)

SQL Server dtexec utility (dtexec.exe)

You are reviewing the design of an existing fact table named factSales, which is loaded from a SQL Azure database by a SQL Server Integration Services (SSIS) package each day.
The fact table has approximately 1 billion rows and is dimensioned by product, sales date, and sales time of day.

The database administrator is concerned about the growth of the database. Users report poor reporting performance against this database. Reporting requirements have recently changed and the only remaining report that uses this fact table reports sales by product name, sale month, and sale year. No other reports will be created against this table.
You need to reduce the report processing time and minimize the growth of the database.
What should you do?

Partition the table by product type.

Create a view over the fact table to aggregate sales by month.

Change the granularity of the fact table to month.

Create an indexed view over the fact table to aggregate sales by month.

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?

Create a time mapping table.

Change the level of granularity in both fact tables to be the same.

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

Merge the fact tables.

You are designing a data warehouse for a software distribution business that stores sales by software title. It stores sales targets by software category. Software titles are classified into subcategories and categories. Each software title is included in only a single software subcategory, and each subcategory is included in only a single category.
The data warehouse will be a data source for an Analysis Services cube.
The data warehouse contains two fact tables:
– factSales, used to record daily sales by software title
– factTarget, used to record the monthly sales targets by software category
Reports must be developed against the warehouse that reports sales by software title, category and subcategory, and sales targets.
You need to design the software title dimension. The solution should use as few tables as possible while supporting all the requirements.
What should you do?

Create three software tables, dimSoftware, dimSoftwareCategory. And dimSoftwareSubcategory and a fourth bridge table that joins software titles to their appropriate category and subcategory table records with foreign key constraints. Direct the cube developer to use key granularity attributes.

Create three software tables, dimSoftware, dimSoftwareCategory, and dimSoftwareSubcategory. Connect factSales to all three tables and connect factTarget to dimSoftwareCategory with foreign key constraints. Direct the cube developer to use key granularity attributes.

Create one table, dimSoftware, which contains Software Detail, Category, and Subcategory columns. Connect factSales to dimSoftware with a foreign key constraint. Direct the cube developer to use a non-key granularity attribute for factTarget.

Create two tables, dimSoftware and dimSoftwareCategory. Connect factSales to dimSoftware and fact Target to dimSoftwareCategory with foreign key constraints. Direct the cube developer to use key granularity attributes.

You are designing a data warehouse hosted on SQL Azure. The data warehouse currently includes the dimUser and dimDistrict dimension tables and the factSales fact table. The dimUser table contains records for each user permitted to run reports against the warehouse; and the dimDistrict table contains information about sales districts. The system is accessed by users from certain districts, as well as by area supervisors and users from the corporate headquarters.
You need to design a table structure to ensure that certain users can see sales data for only certain districts. Some users must be permitted to see sales data from multiple districts.
What should you do?

Add a district column to the dimUser table.

Partition the factSales table on the district column.

Create a userDistrict table that contains primary key columns from the dimUser and dimDistrict tables.

For each district, create a view of the factSales table that includes a WHERE clause for the district.

You are developing a data flow transformation to merge two data sources. One source contains product data and the other source contains data about the country in which the product was manufactured. Both data sources contain a two-character CountryCode column and both use SQL Server. Both data sources contain an ORDER BY clause to sort the data by the CountryCode column in ascending order.
You use a Merge Join transformation to join the data.

You need to ensure that the Merge Join transformation works correctly without additional transformations.
What should you do?
(Each answer presents a part of the solution. Choose all that apply.)

Set the appropriate SortKeyPosition properties on the data sources.

Set the ValidateExternalMetaData property on the Merge Join transformation to True.

Set the IsSorted property on both data sources.

Set the MaxBuffersPerlnput property on the Merge Join transformation to 2.

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?

Catalog. create_environment_variable

Catalog. create_environment_reference

Catalog. set_execution_parameter_value

Catalog. set_environment_variable_value

You are developing a SQL Server Integration Services (SSIS) package. The package is stored as the myPackage.dtsx file in the root directory of the C drive of the SSIS server.
You need to run the package from the command prompt.
Which command should you use?

Dtexec /sql myPackage

Dtexec /d “C:\File System\myPackage.dtsx”

Dtexec /f “C:\myPackage.dtsx”

Dtexec /com “myPackage.dtsx”

You are troubleshooting an existing SQL Server Integration Services (SSIS) package. On several occasions, the package execution does not finish and no data seems to have been transferred. You need to ensure that package logging occurs.
Your solution must minimize deployment and development efforts.
What should you do?

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

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

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

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

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

Configure the SSIS solution to use the Project Deployment Model.

Add an OnError event handler to the SSIS project.

Open a command prompt and run the gacutil command.

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

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

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

You are developing a project that contains multiple SQL Server Integration Services (SSIS) packages. The packages will be deployed to the SSIS catalog.
One of the steps in each package accesses an FTP site to download sales transaction data.
You create project parameters to store the username and password that are used to access the FTP site.
You need to ensure that the username and password values are encrypted when they are deployed.
What should you do?

Set the Sensitive property of the parameters to True.

Set the ProtectionLevel property of the package to EncryptSensitiveWithUserKey.

Change the parameters to package parameters.

Change the project to the Legacy Deployment model.

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?

Change the Exhaustive property to True,

Change the similarity threshold to 0.55.

Change the similarity threshold to 0.40.

Increase the maximum number of matches per lookup.

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.
What should you do? (Each correct answer presents part of the solution. Choose all that apply.)

Enable XP Command Shell.

Enable CLR Integration.

Enable OLE Automation.

Start the SQL Server Browser service.

Enable Cross Database Ownership Chaining.

Start the SQL Server Agent service.

Enable Ad Hoc Remote Queries.

You are maintaining a Data Quality Services (DQS) environment. The production server failed and a new server has been set up. The DQS databases are restored to a new server.
All the appropriate permissions are granted. DQS users are experiencing issues connecting to the new Data Quality Server.
You need to enable users to connect to the new server.
Which Surface Area Configuration property should you enable?

AdHocRemoteQueriesEnabled

SoapEndpointsEnabled

ClrlntegrationEnabled

RemoteDacEnabled

OleAutomationEnabled

XpCmdShellEnabled

Occasionally a job that executes an existing SQL Server Integration Services (SSIS) package does not complete and nothing is processed.
You need to ensure that package logging occurs.
Your solution must minimize deployment and development efforts.
What should you do?

Create a reusable custom logging component.

Use the gacutil command.

Use the Project Deployment Wizard.

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

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

Create an OnError event handler.

Use the dtutil /copy command.

Deploy the package by using an msi file.

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

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

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

You are writing a SQL Server Integration Services (SSIS) package that transfers data from a legacy system. Data integrity in the legacy system is very poor.
Invalid rows are discarded by the package but must be logged to a CSV file for auditing purposes.
You need to establish the best technique to log these invalid rows while minimizing the amount of development effort.
What should you do?

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

Deploy the package by using an msi file.

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

uses the dtutil /copy command.

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

Create an OnError event handler.

Uses the Project Deployment Wizard.

Use the gacutil command.

Create a reusable custom logging component.

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

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

You are developing a project that contains multiple SQL Server Integration Services (SSIS) packages. The packages will be deployed to the SSIS catalog.
One of the steps in each package accesses an FTP site to download data files.
You create project parameters to store the username and password that are used to access the FTP site.
You need to ensure that the username and password values are encrypted when they are deployed.
What should you do?

Convert the parameters to package parameters.

Set the Sensitive property of the parameters to True.

Set the ProtectionLevel property of the package to EncryptSensitiveWithPassword.

Convert the project to the Legacy Deployment model.

A SQL Server Integration Services (SSIS) package was deployed two weeks ago with the Project Deployment Model. Sometimes the package is started as part of a multistep SQL job.
At other times, the package is started manually by a database administrator 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 executes.
How can you find this information?

In the SSISDB.[catalog], query the .[executions] view.

In the SSISDB.[catalog] , query the [event_messages] view.

In SQL Server Management Studio, view the SQL Agent Job History.

In SQL Server Management Studio, view the SQL Agent Error Log.

In SQL Server Management Studio, view the SQL Server Log.

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

Enable CLR Integration.

Enable FILESTREAM with Full Access.

Enable the Resource Governor.

Change the recovery mode of the msdb database to FULL.

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

Start the SQL Server Browser service.

Start the SQL Server Agent service.

SQL Server Configuration Manager

SQL Server Agent

SSIS Upgrade Wizard in SQL Server Management Studio

SQL Server DTExecUI utility (dtexecui.exe)

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?

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

Create a reusable custom logging component.

Create an OnError event handler.

Use the gacutil command.

Use the dtutil /copy command.

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

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

Use the Project Deployment Wizard.

Deploy the package by using an msi file.

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

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

You are deploying a project to the SQL Server Integration Services (SSIS) catalog. You need to ensure that the project is deployed to the production environment with the least amount of administrative effort. What should you do?

Create an On Error event handler.

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

Use the gacutil command.

Create a reusable custom logging component.

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

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

Use the Project Deployment Wizard.

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

Use the dtutil /copy command.

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

Deploy the package by using an msi file.

You are developing a SQL Server Integration Services (SSIS) package to implement an incremental data load strategy. The package reads rows from a source system and compares them to rows in a destination system. New rows will be inserted and changed rows will be updated. You have used a Lookup transformation and a Conditional Split transformation. The Lookup transformation joins the source and destination table on the business key, and includes all columns from the destination table in the data flow output. The Conditional Split transformation inspects the destination columns and directs data flow to either insert new records or update existing records. You need to configure the Lookup transformation to ensure that all records flow to the Conditional Split transformation, regardless of whether the rows match an existing row in the destination table.

Which setting should you select from the OLE DB Connection manager?

Ignore Failure

Redirect rows to error output

Fail component

Redirect rows to no match output

You are using the Master Data Services (MDS) Add-in for Excel to configure the entities in a model. The model consists of two entities: one named Customer and one named State. You opened the Customer entity. Currently, data stewards can enter any text value in the Customer entity’s State attribute. You must restrict the entry of values in the State attribute to members defined in the State entity. You need to configure the State attribute of the Customer entity.

In Excel, under Master Data, which command should you use/click on?

Combine Data

Apply Rules

Show Status

Match Data

Show Details

Create Entity

Attribute Properties

Settings

Your company is evaluating the data cleansing capabilities of SQL Server Data Quality Services (DQS). A stand-alone server will be used to host DQS, as well as all related services necessary to run this service. You need to install the DQS components on the server by using the minimum amount of steps possible. You also need to grant permissions to a login named DataStewards to enable DataStewards to edit and execute DQS projects.

Which step is not one of the steps in the process?

Install the Database Engine, Data Quality Client, and Data Quality Server.

Add the DataStewards user to the dqs_kb_editor role.

Run the Data Quality Server Installer.

Add the DataStewards user to the dqs_kb_operator role.

Add a user named DataStewards to the DQS_MAIN database based on the DataStewards login.

You are developing a SQL Server Integration Services (SSIS) package. You use a Data Profiling task to examine the data from a source system. You need to establish:

The minimum and maximum dates for the datetime columns in the source data. The minimum, maximum, and average values for numeric columns in the source data.

You need to use the appropriate profile type in the Data Profiling task. Which profile type should you use in the Data Profiling Task Editor?

Candidate Key Profile Request

Column Length Distribution Profile Request

Column Null Ratio Profile Request

Column Pattern Profile Request

Column Statistics Profile Request

Column Value Distribution Profile Request

Functional Dependency Profile Request

Value Inclusion Profile Request

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?

All

All with old values

Net

Net with update mask

Net with merge

You are developing a SQL Server Integration Services (SSIS) package. OnError and OnWarning events must be logged for viewing in the built-in SSIS reports by using SQL Server Management Studio. You need to execute the package and minimize the number of event types that are logged. Which Advanced Logging Level setting should you use?

None

Basic

Performance

Verbose

You develop a SQL Server Integration Services (SSIS) package in a project by using the Project Deployment Model. It is regularly executed within a multi-step SQL Server Agent job. You make changes to the package that should improve performance. You need to establish if there is a trend in the durations of the next 10 successful executions of the package. You need to use the least amount of administrative effort to achieve this goal. What should you do?

After 10 executions, view the job history for the SQL Server Agent job.

After 10 executions, in SQL Server Management Studio, view the Execution Performance subsection of the All Executions report for the project.

Enable logging to the Application Event Log in the package control flow for the On information event. After 10 executions, view the Application Event Log.

Enable logging to an XML file in the package control flow for the OnPostExecute event. After 10 executions, view the XML file.

You are performance tuning a SQL Server Integration Services (SSIS) package to load sales data from a source system into a data warehouse that is hosted on Windows Azure SQL Database. The package contains a data flow task that has 10 source-to-destination execution trees. Only five of the source-to-destination execution trees are running in parallel. You need to ensure that all the execution trees run in parallel. What should you do?

Add OnError and OnWarning event handlers.

Create a new project and add the package to the project.

Query the ExecutionLog table.

Add an Execute SQL task to the event handlers.

You are the administrator of a server that hosts Data Quality Server for a large retail company. The server had a hardware failure during business hours. You need to restore the server that hosts Data Quality Server to another server. You have a recent backup of all the required databases. What should you do? (Each correct answer presents part of the solution. Choose all that apply.)

Restore the DQS_MAIN, DQS_PROJECTS, and DQS_STAGING_DATA databases to another server as soon as possible.

Execute the DQS_MAIN.internal_core.RestoreDQDatabases stored procedure with the appropriate parameter.

Restore only the DQS_MAIN and DQS_STAGING_DATA databases to another server as soon as possible.

Execute the DQS_MAIN.internal_core.InitServer stored procedure with the appropriate parameter.

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?

Create a SQL Common Language Runtime (SQLCLR) component that records all transformation steps in a Microsoft SharePoint list.

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

Develop a custom data lineage solution.

Develop a Data Quality Services (DQS) solution.

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. What should you use?

Script component

Custom component

XML source

Script task

You are a data warehouse developer responsible for developing data cleansing processes. Duplicate employees exist in an employee dimension. You need to map, discover, and manage domain values based on the employee dimension. Which Data Quality Services (DQS) option should you use?

New Knowledge Base

Open Knowledge Base

New Data Quality Project

Open Data Quality Project

You are developing a SQL Server Integration Services (SSIS) package. The package contains a user defined variable named ©Queue which has an initial value of 10. The package control flow contains many tasks that must repeat execution until the ©Queue variable equals 0. You need to enable the tasks to be grouped together for repeat execution. Which Control Flow item should you add to the package from the SSIS Toolbox?

Data Flow Task

Execute SQL Task

For Loop Container

Foreach Loop Container

Sequence Container

Analysis Services Processing Task

Bulk Insert Task

Data Profiling Task

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?

Cleansing

Matching

Knowledge Discovery

Matching Policy

You are developing a SQL Server Integration Services (SSIS) project to read and write data from a Windows Azure SQL 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 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. What should you do?

Add a SQLMOBILE connection manager to each package.

Add an ADO.NET project connection manager.

Add a SQLMOBILE project connection manager.

Add an ADO.NET connection manager to each data flow task.

Add a SQLMOBILE connection manager to each data flow task.

Add an ADO.NET connection manager to each package.

Use an SSIS Script task that uses the custom assembly to parse the text data when inserting it.

Use an SSIS Script transformation that uses the custom assembly to parse the text data when inserting it.

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, and use it when inserting data.

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, and use it when inserting data.

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 ParamConnection 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 answer presents a part of the solution. Choose all that apply.)

Create a package parameter named ConnectionName in each package.

Edit each package Connection Manager. Set the ConnectionName property to @[$Project::ParamConnection].

Edit the project Connection Manager in Solution Explorer. Set the ConnectionName property to @ [$Project::ParamConnection].

Set the Sensitive property of the parameter to True.

Create a project parameter named ParamConnection.

Set the Required property of the parameter to True.

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. Which data flow source should you use?

Raw File source

XML source

Custom source component

Flat File source

You are deploying a new SQL Server Integration Services (SSIS) package to five servers. The package must meet the following requirements:

.NET Common Language Runtime (CLR) integration in SQL Server must not be enabled.
The Connection Managers used in the package must be configurable without editing and redeploying the package.
The deployment procedure must be automated as much as possible.
Performance must be maximized.

You need to set up a deployment strategy that meets the requirements. What should you do?

Add an OnError event handler to the SSIS project.

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

Open a command prompt and run the gacutil command.

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

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

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

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

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

Configure the SSIS solution to use the Project Deployment Model.

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

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

You are the data steward at your company. Duplicate customers exist in a Microsoft Excel workbook. You create a Data Quality Services (DQS) knowledge base and matching policy to identify these duplicate customers. You need to identify the duplicate customers. Which DQS feature/option should you use?

New Knowledge Base

Open Knowledge Base

New Data Quality Project

Open Data Quality Project

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?

All

All with old values

Net

Net with update mask

Net with merge

You are developing a SQL Server Integration Services (SSIS) package. The package must run a parameterized query against a Windows Azure SQL Database. You need to use the least amount of development effort to meet the package requirement. Which task from the SSIS Toolbox should you use?

Data Flow Task

Execute SQL Task

Execute T-SQL Statement Task

Expression Task

Script Task

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?

Add and edit a Foreach Loop container.

Open the Data Flow designer of the package.

Set the enumerator property to Foreach NodeList Enumerator and select the variable that contains the ADO object source.

Open the Control Flow designer of the package.

Add and edit a Sequence container.

Set the enumerator property to Foreach ADO Enumerator and select the variable that contains the ADO object source.

Your team is creating SQL Server Integration Services (SSIS) packages that have several dependencies. The packages use parameters for configuration purposes. Your company’s IT policies include the following change control requirements:

After a package is ready for deployment, your team must hand over the process to junior IT personnel.
The process must guarantee that when a package has been validated, that same package (and all its dependencies) is deployed to production.
The process must be repeatable and reliable and must be executed with the least administrative and training effort by junior IT personnel.

You need to use the most appropriate deployment unit to satisfy the company policies, while minimizing issues such as incorrect version or configuration. Which type of deployment unit should you use?

Dtsx

SSISdeploymentmanifest

Msi

Ispac

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?

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

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.

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

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

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?

Conditional Split

Pjvot

Multicast

Lookup

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?

Install the Analysis Services OLE DB Provider.

Run the DQSInstaller.exe command.

Run the Configuration component in the Data Quality Client.

Make the data available for DQS operations.

You are developing a SQL Server Integration Services (SSIS) project by using the Project Deployment Model. The project will be deployed to an SSIS catalog folder where Environments have already been created. You need to deploy the project. What should you do?

Use an event handler for OnError for the package.

Use an event handler for OnError for each data flow task.

Use an event handler for OnTaskFailed for the package.

View the job history for the SQL Server Agent job.

View the All Messages subsection of the All Executions report for the package.

Store the System::SourceID variable in the custom log table.

Store the System::ServerExecutionID variable in the custom log table.

Store the System::ExecutionInstanceGUID variable in the custom log table.

Enable the SSIS log provider for SQL Server for OnError in the package control flow.

Enable the SSIS log provider for SQL Server for OnTaskFailed in the package control flow,

Deploy the project by using dtutil.exe with the /COPY DTS option.

Deploy the project by using dtutil.exe with the /COPY SQL option.

Deploy the .ispac file by using the Integration Services Deployment Wizard.

Create a SQL Server Agent job to execute the SSISDB.catalog.validate_project stored procedure.

Create a SQL Server Agent job to execute the SSISDB.catalog.validate_package stored procedure.

Create a SQL Server Agent job to execute the SSISDB.catalog.create_execution and SSISDB.catalog.start_execution stored procedures.

Create a table to store error information. Create an error output on each data flow destination that writes OnError event text to the table.

Create a table to store error information. Create an error output on each data flow destination that writes OnTaskFailed event text to the table.

You are developing a SQL Server Integration Services (SSIS) package to load data into a data warehouse. The package consists of several data flow tasks. The package experiences intermittent errors in the data flow tasks. If any data flow task fails, all package error information must be captured and written to a SQL Server table by using an OLE DB connection manager. You need to ensure that the package error information is captured and written to the table. What should you do?

Use an event handler for OnError for the package.

Use an event handler for OnError for each data flow task.

Use an event handler for OnTaskFailed for the package.

View the job history for the SQL Server Agent job.

View the All Messages subsection of the All Executions report for the package.

Store the System::SourceID variable in the custom log table.

Store the System::ServerExecutionID variable in the custom log table.

Store the System::ExecutionInstanceGUID variable in the custom log table.

Enable the SSIS log provider for SQL Server for OnError in the package control flow.

Enable the SSIS log provider for SQL Server for OnTaskFailed in the package control flow.