Quiz Maker LogoMake your own QuizCopy & Edit
Make your own quizManage my QuizzesPlans / FeaturesGuides and HelpContactPrivacy & Security

Microsoft 70-463 Practice Questions 3

A sleek and modern image of a data engineer working on SQL Server Integration Services (SSIS) packages, with various charts and database icons in the background, symbols of data analytics and cloud computing.

Master SQL Server Integration Services (SSIS) with This Quiz

Test your knowledge of SQL Server Integration Services (SSIS) with this comprehensive quiz. Perfect for data professionals, this quiz covers key topics, including data flow tasks, error handling, and managing data integrity. Whether you're preparing for a certification exam or looking to refresh your skills, this quiz is tailored for you.

  • 30 carefully crafted questions
  • Covering various SSIS functionalities
  • Score and review your answers instantly
61 Questions15 MinutesCreated by TransformingData102
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.
 
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.
You are developing a SQL Server Integration Services (SSIS) package that downloads data from a Windows Azure SQL Database. A stored procedure will be called in an Execute SQL task by using an ODBC connection. This stored procedure has only the @CustomerID parameter of type INT. A project parameter named CustID will be mapped to the stored procedure parameter @CustomerID. You need to ensure that the value of the CustID parameter is passed to the @CustomerID stored procedure parameter.
 
In the Parameter Mapping tab of the Execute SQL task editor, choose the values that should be assigned to the following parameters:
 
Variable Name : ________
Direction : _____________
Data Type : ____________
Parameter Name : _______
@CustomerID
$Project::CustID
User::CustID
Input
Output
SQL_INTEGER
SQL_SMALLINT
SQL_VARCHAR
0
1
You are developing a SQL Server Integration Services (SSIS) package that loads data into a data warehouse hosted on Windows Azure SQL Database. You must combine two data sources together by using the ProductID column to provide complete details for each record. The data retrieved from each data source is sorted in ascending order by the ProductID column. You need to develop a data flow that imports the data while meeting the requirements. How should you develop the data flow?
Merge
Merge Join
Sort
Union All
You are developing a SQL Server Integration Services (SSIS) package. The package contains several tasks that must repeat until an expression evaluates to FALSE. You need to add and configure a container to enable this design. Which three actions should you perform in sequence?
Configure the EvalExpression property.
Configure the Enumerator property.
Open the Data Flow designer of the package.
Add and edit a For Loop container.
Add and edit a Foreach Loop container
Open the Control Flow designer of the package.
You administer a SQL Server Integration Services (SSIS) solution in the SSIS catalog. A SQL Server Agent job is used to execute a package daily with the basic logging level. Recently, the package execution failed because of a primary key violation when the package inserted data into the destination table. You need to identify all previous times that the package execution failed because of a primary key violation. 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 Windows Azure SQL Database database. The package consists of several data flow tasks. The package has the following auditing requirements:

 

Ÿ   If a data flow task fails, a Transact-SQL (T-SQL) script must be executed.

Ÿ   The T-SQL script must be executed only once per data flow task that fails, regardless of the nature of the error.

 

You need to ensure that auditing is configured to meet these requirements. 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.va!idate_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 OnTaskFailed event text to the table.

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.

---------
EmployeeID int
EmployeeNum char(10)
LastName nvarchar(200)
FirstName nvarchar(200)
MiddleName nvarchar(200)
DateHired date
DepartmentID int
JobTitle varchar(200)
ReportsToID int
---------

EmployeeID(pk) : Uniquely identifies the employee record in the table. Used throughout the database by all the other tables that reference the Employee table.

EmployeeNum : An alphanumeric value calculated according to company requirements. Has to be unique within the Employee table. Exists only within the Employee table

DepartmentID : References another table named Department that contains data for each department in the company.

ReportsToID : Contains the EmployeeID of the manager to whom an employee reports.

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 a create a unique constraint?

EmployeeID
EmployeeNum
DepartmentID
ReportsToID
DateHired
FirstName
LastName
MiddleName

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.

---------
EmployeeID int
EmployeeNum char(10)
LastName nvarchar(200)
FirstName nvarchar(200)
MiddleName nvarchar(200)
DateHired date
DepartmentID int
JobTitle varchar(200)
ReportsToID int
---------

EmployeeID(pk) : Uniquely identifies the employee record in the table. Used throughout the database by all the other tables that reference the Employee table.

EmployeeNum : An alphanumeric value calculated according to company requirements. Has to be unique within the Employee table. Exists only within the Employee table

DepartmentID : References another table named Department that contains data for each department in the company.

ReportsToID : Contains the EmployeeID of the manager to whom an employee reports.

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?

 

DateHired
DepartmentID
EmployeeID
EmployeeNum
FirstName
JobTitle
LastName
MiddleName
ReportsToID
You are a database developer of a Microsoft SQL Server 2012 database. You are designing a table that will store Customer data from different sources. The table will include a column that contains the CustomerID from the source system and a column that contains the SourceID. A sample of this data is as shown in the following table. You need to ensure that the table has no duplicate CustomerID within a SourceID. You also need to ensure that the data in the table is in the order of SourceID and then CustomerID. Which Transact- SQL statement should you use?
CREATE TABLE Customer (SourceID int NOT NULL IDENTITY, CustomerID int NOT NULL IDENTITY, CustomerName varchar(255) NOT NULL);
CREATE TABLE Customer (SourceID int NOT NULL, CustomerID int NOT NULL PRIMARY KEY CLUSTERED, CustomerName varchar(255) NOT NULL);
CREATE TABLE Customer (SourceID int NOT NULL PRIMARY KEY CLUSTERED, CustomerID int NOT NULL UNIQUE, CustomerName varchar(255) NOT NULL);
CREATE TABLE Customer (SourceID int NOT NULL, CustomerID int NOT NULL, CustomerName varchar(255) NOT NULL, CONSTRAINT PK_Customer PRIMARY KEY CLUSTERED (SourceID, CustomerID));

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?  Choose the two items that complete the script.

Use [master]
GO
Create Login [MYDOMAIN\dqsuser] From Windows With Default_Database=[ma.....
GO
USE _______________
GO
Create User [MYDOMAIN\dqsuser] For Login [MYDOMAIN\dqsuser]
GO
Alter Role __________________ Add Member [MYDOMAIN\dqsuser]
GO

Msdb
Master
Dqs_main
Dqs_kb_editor
Dqs_kb_operator
Dqs_administrator

A new dedicated server is used to execute resource-intensive SQL Server Integration Services (SSIS) 2012 packages. The environment that you are deploying the packages to has the following constraints:

 

Ÿ   The operating system is Windows Server 2008 R2.

Ÿ   The SSIS packages are stored in the SSIS catalog.

Ÿ   Some of these SSIS packages use 32-bit custom components.

 

You need to install only the components that are required to deploy and run the packages on the new server. Which three actions should you perform in sequence?

Configure the MsDtsSrvr.ini.xml file to use the msdb database.
Install Database Engine Services, Integration Services, and Client Tools SDK.
Install Microsoft NET Framework 3.5 SP1.
Install Database Engine Services, Integration Services, and SQL Server Data Tools.
Install Database Engine Services, Integration Services, and Management Tools - Basic.
Create the SSIS catalog.
Install Database Engine Services and Integration Services.
You are developing a SQL Server Integration Service (SSIS) package. The package loads a customer dimension table by using a data flow task. Changes to the customer attributes must be tracked over time. You need to produce a checksum value to identify the rows that have changed since the last Extract, Transform and Load (ETL) process execution. You need to use the least amount of development effort to achieve this goal. Which transformation should you use?
Cache Transform
CDC Splitter
Character Map
Data Conversion
Import Column
Script Component
A SQL Server Integration Services (SSIS) package is designed to download data from a financial database hosted in SQL Azure. The connection string to the financial database is defined as a project parameter named FinConStr. The parameter value must be stored securely and must be set explicitly every time the package is executed. You need to configure the parameter to meet the requirements. What setting should the Sensitive and Required attributes for the FinConStr parameter be set to?
True
False
You are the Master Data Services (MDS) administrator at your company. An existing user must be denied access to a certain hierarchy node for an existing model. You need to configure the user’s permissions. Which user management menu item should you select?
General
Membership
Functions
Models
Hierarchy members

You are developing a SQL Server Integration Services (SSIS) package. An Execute SQL task in the package checks product stock levels and sets a package variable named InStock to TRUE or FALSE depending on the stock level found. After the successful execution of the Execute SQL task, one of two data flow tasks must run, depending on the value of the InStock variable. You need to set the precedence constraints. In the Precedence Constraint Editor, which value for the evaluation operation should you use?

Constraint
Expression
Expression and Constraint
Expression or Constraint
You are creating a SQL Server Integration Services (SSIS) package that implements a Type 3 Slowly Changing Dimension (SCD). You need to add a task or component to the package that allows you to implement the SCD logic. What should you use?
A Data Conversion component
An Execute SQL task that executes a MERGE statement on the database
A Merge component
An Expression task

You are designing a data warehouse for a fresh food distribution business that stores sates by individual product. It stores sales targets by product category. Products are classified into subcategories and categories. Each product is included in only a single product 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 product

Ÿ   factProductTarget, used to record the monthly sales targets by product category

Reports must be developed against the warehouse that reports product sales by product, category and subcategory, and product sales targets. You need to design the product dimension. The solution should use as few tables as possible while supporting all the requirements. What should you do?

Create two product tables, dimProduct and dimProductCategory. Connect factSales to dimProduct and factProductTarget to dimProductCategory with foreign key constraints. Direct the cube developer to use key granularity attributes.
Create one product table, dimProduct, which contains product detail, category, and subcategory columns. Connect factSales to dimProduct with a foreign key constraint. Direct the cube developer to use a non-key granularity attribute for factProductTarget.
Create three product tables, dimProduct, dimProductCategory, and dimProductSubcategory, and a fourth bridge table that joins products to their appropriate category and subcategory table records with foreign key constraints. Direct the cube developer to use key granularity attributes.
Create three product tables, dimProduct, dimProductCategory, and dimProductSubcategory. Connect factSales to all three product tables and connect factProductTarget to dimProductCategory with foreign key constraints. Direct the cube developer to use key granularity attributes.
You are reviewing the design of an existing fact table named factSales, which is loaded incrementally from a SQL Azure database by a SQL Server Integration Services (SSIS) package each hour. The fact table has approximately 4 billion rows and is dimensioned by product, sales date, and sales time of day.

The database administrator is concerned about the rapid growth of the database and users experience 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?
Create an indexed view over the fact table to sum orderTotal by month.
Create a view over the fact table to sum orderTotal by month.
Change the granularity of the fact table to month.
Partition the fact table by productKey.

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?

In the deployed project, set the ConnectionString property of the Connection Manager to use the ConnectionString Environment Variable.
Deploy the project to the test and production environments.
Add permissions to the Test and Production Environments.
Execute the deployed package by using the required Environment.
Add the Environment reference to the deployed project.
In the deployed package, set the ConnectionString property of the Connection Manager to use the ConnectionString Environment Variable.
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.
You administer a Microsoft SQL Server 2012 database. The database contains a table that has the following definition:
 
Create Table [Sales].[Customer] (
 
CustomerID int NOT NULL,
CustomerName nvarchar(50) NOT NULL,
TerritoryID int NULL,
LastContactDate datetimeoffset NULL,
CustomerType nchar(1) NOT NULL,
Notes varchar(250) NULL
 

You want to export data from the table to a flat file by using the SQL Server Import and Export Wizard. You need to ensure that the following requirements are met:

 

The first row of the file contains the first row of data.

Each record is of the same length.

The date follows the U.S. Date format.

The file supports international characters.

 

Which options in the wizard do you need to modify?

Current Wizard Configuration:

Destination: Flat File Destination
File name: C:\Employee.csv
Local: English (United States)
Code page: 1252 (ANSI Latin I)
Format: <blank>
Text qualifier: <none>
Column names in the first row: unchecked

Destination
File name
Locale
Code page
Format
Text qualifier
Column names in the first data row
You are developing a SQL Server Integration Services (SSIS) project by using the Project Deployment model. The project contains many packages. It is deployed on a server named SQLTest1. The project will be deployed to several servers that run SQL Server 2012. The project accepts one required parameter. The data type of the parameter is a string. A SQL Agent job is created that will call the Loading.dtsx package in the project. A job step is created for the SSIS package. The job must pass the value of an SSIS Environment Variable to the project parameter. The value of the Environment Variable must be configured differently on each server that runs SQL Server. The value of the Environment Variable must provide the server name to the project parameter. You need to configure SSIS on the SQLTest1 server to pass the Environment Variable to the package. Which four actions should you perform in sequence by using SQL Server Management Studio?
In the SSIS catalog, create an Environment named EnvTest
In the EnvTest Environment, create an Environment Variable. Name the Environment variable EnvServer. Set the Environment Variable value to SQLTest1
In the SQL Agent Job, choose the EnvTest Environment in the Configuration tab of the SQL Integration Services job step
In the SSIS catalog, create a folder name EnvTest
In the project configuration, create an Environment reference to EnvTest. Assign the value of the EnvServer Environment Variable to the project parameter.
In the project configuration, change the value of the project parameter to EnvTest.

You are designing a SQL Server Integration Services (SSIS) package. The package moves order-related data to a staging table named Order. Every night the staging data is truncated and then all the recent orders from the online store database are inserted into the staging table. Your package must meet the following requirements:

Ÿ   If the truncate operation fails, the package execution must stop and report an error.

Ÿ   If the Data Flow task that moves the data to the staging table fails, the entire refresh operation must be rolled back.

Ÿ   For auditing purposes, a log entry must be entered in a SQL log table after each execution of the Data Flow task.

The TransactionOption property for the package is set to Required. You need to design the package to meet the requirements. How should you design the control flow for the package?  Which options filling the designated spaces?

PACKAGE:

Truncate Staging Order Table     [   new option here   ]
\/
[   constraint setting here   ]
\/
Move Data to Staging Order Table     [   new option here   ]
\/
[   constraint setting here   ]
\/
Log Execution    [   new option here   ]

 

TransactionOption: Required
TransactionOption: Supported
TransactionOption: Not Supported
Precedence Constraint: Failure
Precedence Constraint: Success
Precedence Constraint: Completion

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?  Which options fill in the designated areas?

OLE DB Source
         \/
[     item goes here    ]
              \/
            Lookup
             \/                            \/
[ item goes here ]        [  item goes here   ]
     \/                                     \/
OLE DB Destination               Flat File Destination

Lookup Error Output
OLE DB Error Output
Lookup Match Output
OLE DB Source Output
Lookup No Match Output
You are building a SQL Server Integration Services (SSIS) package to load product data sourced from a SQL Azure database to a data warehouse. Before the product data is loaded, you create a batch record by using an Execute SQL task named Create Batch. After successfully loading the product data, you use another Execute SQL task named Set Batch Success to mark the batch as successful.
 
Create Batch
\/
Load Products
\/
Set Batch Success
 
You need to create and execute an Execute SQL task to mark the batch as failed if either the Create Batch or Load Products task fails. Which three steps should you perform in sequence?
Set the Multiple Constraints option to Logical OR.
Set the Evaluation Operation option to Expression OR Constraint.
Connect the Create Batch and Load Products tasks to the Set Batch Failure task with failure constraints.
Create an Execute SQL task named Set Batch Failure to mark the batch as failed.
Set the Multiple Constraints option to Logical AND.
Connect the Create Batch and Load Products tasks to the Set Batch Failure task with completion constraints.
Set the ForceExecutionValue property of the Create Batch task to True.
You are developing a data flow to load sales data into a fact table. In the data flow, you configure a Lookup Transformation in full cache mode to look up the product data for the sale. The lookup source for the product data is contained in two tables. You need to set the data source for the lookup to be a query that combines the two tables. Which page of the Lookup Transformation Editor should you select to configure the query?
Connection
Columns
Advanced
Error Output
You are creating a sales data warehouse. When a product exists in the product dimension, you update the product name. When a product does not exist, you insert a new record. In the current implementation, the DimProduct table must be scanned twice, once for the insert and again for the update. As a result, inserts and updates to the DimProduct table take longer than expected. You need to create a solution that uses a single command to perform an update and an insert. How should you use a MERGE T-SQL statement to accomplish this goal?
 
MERGE dbo.DimProduct AS   [Insert Statement Here]
 
USING (SELECT ProductID, ProductName, ProductColor, ProductCategory
     FROM dbo.StagingProduct) AS   [Insert Statement Here]
ON (Target.ProductID = Source.ProductID)
 
WHEN   [Insert Statement Here]
 
   UPDATE SET Target.ProductName - Source.ProductName
 
WHEN   [Insert Statement Here]
 
   INSERT (ProductID, ProductName, ProductColor, Source.ProductCategory)
   VALUES (Source.ProductID, Source.ProductName, Source.ProductColor, Source.ProductCategory)
OUTPUT $action, Inserted.*, Deleted.*;
Source
Target
Product
Inserted
MATCHED THEN
NOT MATCHED BY TARGET THEN
NOT MATCHED BY PRODUCT AND (Target.ProductID is NULL) THEN
MATCHED AND (Target.ProductID is NULL) THEN

You are developing a SQL Server Integration Services (SSIS) package that imports unsorted data into a data warehouse hosted on SQL Azure. You have the following requirements:

Ÿ   A destination table must contain all of the data in two source tables.

Ÿ   Duplicate records must be inserted into the destination table.

You need to develop a data flow that imports the data while meeting the requirements. How should you develop the data flow?

Table Test 1                              Table Test 2
               \/                                   \/
             [        item goes here               ]
                                \/
                         Table Test 3

Merge
Merge Join
Sort
Union All

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 for the ExpectedEndDate?

Fixed Attribute
Changing Attribute
Historical Attribute

You administer a Microsoft SQL Server database. You want to import data from a text file to the database. You need to ensure that the following requirements are met:

Ÿ   Data import is performed by using a stored procedure.

Ÿ   Data is loaded as a unit and is minimally logged.

Which data import command and recovery model should you choose?

BCP
BULK INSERT
Bulk-Logged
OPENDATASOURCE
Full
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 Group, does not yet have a domain. You need to complete the mapping of the Group column. What should you do?
Map a composite domain to the source column.
Create a composite domain that includes the Group column.
Add a domain for the Group column.
Add a column mapping for the Group column.

You are editing a SQL Server Integration Services (SSIS) package that uses checkpoints.

 

The package performs the following steps:

 

  1. Download a sales transaction file by using FTP.
  2. Truncate a staging table.
  3. Load the contents of the file to the staging table.
  4. Merge the data with another data source for loading to a data warehouse.
 
Package:
FTP Sales Data
\/
Truncate Staging Table
\/
Load Sales to Staging
\/
Merge Sales
 
The checkpoints are currently working such that if any of the four steps fail, the package will restart from the failed step the next time it executes. You need to modify the package to ensure that if either the Truncate Staging Table or the Load Sales to Staging task fails, the package will always restart from the Truncate Staging Table task the next time the package runs. Which three steps should you perform in sequence?
Set the DelayValidation property of the container to true.
Set the ForceExecutionResult property of the Truncate Staging Table task to Success
Create a task to delete the checkpoint file if the Truncate Staging Table task Fails
Set the FailPackageOnFailure property of the container to True
Move the Truncate Staging Table and Load Sales to Staging tasks to add a new Sequence container and set their FailParentOnFailure properties to True
Set the ForceExecutionResult property of the Truncate Staging Table task to Completion
Open the Data Flow Page
Open the SSIS Toolbox
A new SQL Server Integration Services (SSIS) project is deployed to the SSIS catalog. To troubleshoot some data issues, you must output the data streaming through several data flows into text files for further analysis. You have the list of data flow package paths and identification strings of the various task components that must be analyzed. You need to create these output files with the least amount of administrative and development effort. Which three stored procedures should you execute in sequence?
Catalog. create_folder
Catalog. Create_Execution_dump
Catalog. add_data_tap
Catalog. configure_catalog
Catalog. add_data_tap_by_guid
Catalog. create_Execution
Catalog. Start_execution
You are building a fact table in a data warehouse. The table must have a columnstore index. The table cannot be partitioned. You need to design the fact table and load it with data. Which three actions should you perform in sequence?
Enable XVelocity on the server.
Create the columnstore index.
Load the data.
Create the table
Set the Allow Snapshot Isolation database property to True
You are developing a SQL Server Integration Services (SSIS) package that is ready for deployment to a production server. The package contains sensitive information secured by using the EncryptSensitiveWithUserKey package protection level. You are preparing the package for deployment by the production operations team. You need to ensure that the production operations team can open and execute the package without re-entering the sensitive information. Which three steps should you perform in sequence?
Open the package in SQL Server Data Tools
In the Properties window, change the ProtectionLevel property to EncryptSensitiveWithPassword and enter the PackagePassword
Use the [Encrypt] option with the required protection level and password
Run the dtexecUI command prompt utility
Click the task which contains the sensitive information
Click the design surface of the Control Flow designer
Run the dtexec command prompt utility
When prompted, reset the package protection level for production
You develop a SQL Server Integration Services (SSIS) project by using the Project Deployment model. The project contains many packages. It is deployed on a server named Development1. The project will be deployed to several servers that run SQL Server 2012. The project accepts one required parameter. The data type of the parameter is a string. A SQL Agent job is created that will call the master.dtsx package in the project. A job step is created for the SSIS package. The job must pass the value of an SSIS Environment Variable to the project parameter. The value of the Environment Variable must be configured differently on each server that runs SQL Server. The value of the Environment Variable must provide the server name to the project parameter. You need to configure SSIS on the Development1 server to pass the Environment Variable to the package. Which four actions should you perform in sequence by using SQL Server Management Studio?
In the SSIS catalog, create a folder named DevelopmentEnv.
In the SSIS catalog, create an Environment named DevelopmentEnv.
In the DevelopmentEnv Environment, create an Environment Variable. Name the Environment Variable PackageEnvironment. Set the Environment Variable value to Development1.
In the project configuration, create an Environment reference to DevelopmentEnv. Assign the value of the PackageEnvironment Environment Variable to the project parameter.
In the project configuration, edit the value of the project parameter to DevelopmentEnv.
In the SQL Agent job, choose the DevelopmentEnv Environment in the Configuration tab of the SQL Server Integration Services job step.
You are editing a SQL Server Integration Services (SSIS) package that contains a task with a sensitive property. You need to create a project parameter and configure it so that its value is encrypted when it is deployed to the SSIS catalog. Which three steps should you perform in sequence?
Select the property to be parameterized and set the scope to project
Edit the parameter and set the Sensitive property to True.
Convert the project to the Legacy Deployment model.
Select the property to be parameterized and set the scope to Package.
Right-click the task and chose Parameterize.
Right-click the task and chose Properties.

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:

Ÿ   Development

Ÿ   QA

Each Environment defines a single Environment Variable named ConnectionString of type string. The value of each variable consists of the connection string to the development or QA data warehouses. You need to be able to execute deployed packages by using either of the defined Environments. Which three actions should you perform in sequence?

In the deployed project, set the ConnectionString property of the Connection Manager to use the ConnectionString Environment Variable
In the deployed package, set the ConnectionString property of the connection Manager to use the ConnectionString Environment Variable
Add the Environment references to the deployed project
Deploy the project to the Development and QA Environments
Add permissions to the Development and QA Environments
Execute the deployed package by using the required Environment
You are validating whether a SQL Server Integration Services (SSIS) package named Master.dtsx in the SSIS catalog is executing correctly. You need to display the number of rows in each buffer passed between each data flow component of the package. Which three actions should you perform in sequence?
Execute a SQL statement with a package name of Master.dtsx against the catalog.executions view and return its execution ID.
Run the Master.dtsx package with its logging level set to Performance.
Execute a SQL statement with the execution ID equal to the previous retrieved execution ID against the catalog.execution_data_statistics view and return the rows_sent column values for all the rows.
Run the Master.dtsx package with the logging level set to Verbose.
Execute a SQL statement with a package name of Master.dtsx against the msdb..syssislog table and return its execution ID.
You plan to deploy a SQL Server Integration Services (SSIS) project by using the project deployment model. You need to monitor control flow tasks to determine whether any of them are running longer than usual. Which three actions should you perform in sequence?
Write a query against the catalog.operation_messages view. Add a calculation to the Query to compare durations to the catalog.executables view
Execute the query
Write a query against the catalog.execution_component_phases view. Add a calculation to the query to compare durations to the catalog.executables view.
Connect to the SSISDB database
Connect to the msdb database
Write a query against the catalog.execution_component_phases view. Add a calculation to the query to compare durations to the catalog.executions view.
Write a query against the catalog.operation_messages view. Add a calculation to the Query to compare durations to the catalog.executions view
A Data Flow task in a SQL Server Integration Services (SSIS) package produces run- time errors. You need to edit the package to log specific error messages. Which three actions should you perform in sequence?
Create an OnError event handler for the Data Flow task.
Define an expression for the ActionAtEvent property
Add a Notify Operator task
Add a WMI Event Watcher task
In the ReadOnlyVariables list, select System::ErrorDescripton and add conditional logic to invoke the FireError method
Add a Script task

You use SQL Server Integration Services (SSIS) for extract, transformation, load (ETL) processing.

Issues concerning addresses are discovered in the data warehouse that you manage. Validation must separate the addresses into three categories:

Ÿ   Valid addresses

Ÿ   Autocorrected addresses

Ÿ   Invalid addresses

You need to enhance the SSIS packages to perform address validation by using an external service. Which transformation should you use?

Package:

OLE DB Source
\/
[ new item here ]
\/
Conditional Split
            \/                                       \/                                         \/
Valid Addresses    |    Autocorrected Addresses    |    Invalid Addresses

Lookup
Term Lookup
Fuzzy Lookup
Fuzzy Grouping
DQS Cleansing
Slowly Changing Dimension

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?

XML configuration file
Environment variable
Registry entry
Parent package variable
SQL Server

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.

Ÿ   Force packages to load all settings in the configuration.

Ÿ   Support Encrypting File System (EFS) formats.

You need to select the appropriate configuration. Which configuration type should you use?

XML configuration file
Environment variable
Registry entry
Parent package variable
SQL Server
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 item for the Data Flow tab should you select from the SSIS Toolbox?
ADO NET Destination
DataReader Destination
Slowly Changing Dimension
Execute Process Task
Script Component
Data Conversion
Conditional Split
You are editing a SQL Server Integration Services (SSIS) project named Project1 in SQL Server Data Tools. A package Connection Manager has been parameterized with project scope. You need to display the parameters that have been generated for the Connection Manager. Which Solution Explorer item should you use?
 
Solution Explorer:
 
Project1
  Project.params
  Connection Manager
  SSIS Package
    Package.dtsx
  Miscellaneous
 
 
Project.params
Connection Manager
SSIS Packages
Package.dtsx
Miscellaneous
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?
On the Collection page, select the Foreach File Enumerator option and configure the directory and files to process.
Open the Control Flow page of the package.
Create a For Loop container.
Create a File System task.
On the Collection page, select the Foreach ADO Enumerator option and configure the ADO object source variable.
Create a Foreach Loop container.
Open the Data Flow page of the package.
You administer a Microsoft SQL Server database. Service accounts for SQL Agent are configured to use a local user. A Microsoft SQL Server Integration Services (SSIS) job step has been created within a SQL Server Agent job. The SSIS package accesses a network share when exporting data from a SQL Server database. When you execute the SQL Server Agent job, it fails due to a permissions failure on a share on a remote server. You need to ensure that the SQL Server Agent job can execute the SSIS package. Which four actions should you perform in sequence?
Add a proxy that references the local user.
Add a proxy that references the credential.
Create a local user account and grant local administrator on the SQL Server instance.
Create a credential that references the local user.
Create a credential that references the domain user.
Assign the proxy to the Operating System subsystem.
Assign the proxy to the SSIS package execution subsystem.
Create a domain user account and grant permissions to the domain user account to access the network share.

You are creating a SQL Server Integration Services (SSIS) package to populate a fact table from a source table. The fact table and source table are located in a SQL Azure database. The source table has a price field and a tax field. The OLE DB source uses the data access mode of Table. You have the following requirements:

Ÿ   The fact table must populate a column named TotalCost that computes the sum of the price and tax columns.

Ÿ   Before the sum is calculated, any records that have a price of zero must be discarded.

You need to create the SSIS package in SQL Server Data Tools. In what sequence should you order four of the listed components for the data flow task?

Conditional Split
Data Conversion
OLE DB Destination
Multicast
Derived Column
Copy Column
Data Profiling Task
OLE DB Source

You are designing a SQL Server Integration Services (SSIS) package to execute 12 Transact-SQL (T-SQL) statements on a SQL Azure database. The T-SQL statements may be executed in any order. The T-SQL statements have unpredictable execution times. You have the following requirements:

    The package must maximize parallel processing of the T-SQL statements.

Ÿ   After all the T-SQL statements have completed, a Send Mail task must notify administrators.

 You need to design the SSIS package. Which three actions should you perform in sequence?

Add a Send Mail task to the control flow. Add a precedence constraint for Completion to the final Execute SQL task and link it to the Send Mail task
Add a Sequence container to the control flow.
Add a Send Mail task to the control flow. Add a precendence constraint for Completion to the Sequence container and link it to the Send Mail task.
Create precedence constraints for Completion between all the Execute SQL tasks.
Add 12 Execute SQL tasks to the control flow and configure the tasks.
Add 12 Execute SQL tasks to the Sequence container and configure the tasks
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?
Run the Configuration component in the Data Quality Client.
Install ADOMD.NET.
Run the Data Quality Server Installer.
Make the data available for DQS operations.
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?
Add a column mapping for the Team Type column.
Add a domain for the Team Type column.
Map a composite domain to the source column.
Create a composite domain that includes the Team Type column.
You are developing a SQL Server Integration Services (SSIS) project that copies a large amount of rows from a SQL Azure database. The project uses the Package Deployment Model. This project is deployed to SQL Server on a test server. You need to ensure that the project is deployed to the SSIS catalog on the production server. 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.
Run the package by using the dtexecui.exe utility and the SQL Log provider.
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 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:
 
Update Recent Orders
\/
Insert New Customers
\/
Insert New Orders
 

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 (IsolationLevel and TransactionOption)?

Chaos
NotSupported
ReadCommitted
ReadUncommitted
RepeatableRead
Required
Serializable
Snapshot
Supported
Unspecified
You are designing an extract, transform, load (ETL) process with SQL Server Integration Services (SSIS). Two packages, Package A and Package B, will be designed. Package A will execute Package B. Both packages must reference a file path corresponding to an input folder where files will be located for further processing. You need to design a solution so that the file path can be easily configured with the least administrative and development effort. Which four actions should you perform in sequence?
Create an SSIS project containing Package A and Package B
Add a project parameter named FilePath.
Add a package parameter named FilePath to Package A and Package B
Deploy the project to the SSIS catalog.
Configure the project to use an Environment that sets the FilePath parameter.
Add a Parent Package Variable configuration in Package B to set the FilePath variable.
Add a configuration file in Package A to set the FilePath value.
Create one SSIS project containing only Package A and another SSIS project containing only Package B.

A SQL Server Integration Services (SSIS) package named DataFeed interacts with an external vendor data feed. The package is executed several times a day, either as part of other packages’ control flow or by itself. The external data feed is unreliable because network failures and slow response times are frequent. The package is currently deployed on the file system. To analyze the reliability of the external data feed, you must collect execution data. Every time the DataFeed package is executed, the following information must be logged:

Ÿ   Start Time

Ÿ   End Time

Ÿ   Execution Result

Ÿ   Execution Duration

You need to design a logging solution that meets the requirements by using the least amount of administrative and development effort. Which three actions should you perform in sequence?

Add OnError and On Warning event handlers
Query the catalog. executable_statistics view
Query the ExecutionLog Table
Deploy the project that contains the package to the SSIS Catalog and execute the package on the server.
Deploy the package to the msdb dtabase.
Add an Execute SQL task to the event handlers.
Query the catalog. execution_data_statistics view.
Create a new Project and add the package to the project.
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?
Use the Expression task.
Use the Script component.
Use the Script task.
Develop a custom task.
Develop a custom component.
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.
Create an OnError event handler.
Use the Integration Services 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.
Deploy the package to the Integration Services catalog by using dtutil and use SQL Server to store the configuration.
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?
Create an Integration Services package and use a Data Profiling transform.
Create a custom .NET application based on the Knowledgebase class.
Create a CLR stored procedure based on the Knowledgebase class.
Create a Master Data Services (MDS) business rule.
Create a data quality project.
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?
Change the TransactionOption property of the package to Required.
Change the TransactionOption property of all three Execute SQL tasks to Required.
Move the three Execute SQL tasks into a Sequence container.
Move the three Execute SQL tasks into a Foreach Loop container.

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?

OnWarning
OnVariableValueChanged
OnExecStatusChanged
OnPostExecute
{"name":"Microsoft 70-463 Practice Questions 3", "url":"https://www.quiz-maker.com/QPREVIEW","txt":"Test your knowledge of SQL Server Integration Services (SSIS) with this comprehensive quiz. Perfect for data professionals, this quiz covers key topics, including data flow tasks, error handling, and managing data integrity. Whether you're preparing for a certification exam or looking to refresh your skills, this quiz is tailored for you.30 carefully crafted questionsCovering various SSIS functionalitiesScore and review your answers instantly","img":"https:/images/course8.png"}

More Quizzes

Microsoft 70-463 Practice Questions 4
31160
Master Data Management
1050
HANA DATA SERVICES 01
8040132
Datawarehouse
840
ADW Bootcamp Week 1 PreQuiz
10519
ADW Bootcamp Week 3 PreQuiz
10517
Power BI
327
Big Data Analytics
10526
Big Data 1
94242
ETL Testing
1050
Microsoft 70-463 Practice Questions 2
61300
ADW Bootcamp Week 2 PreQuiz
13620
Quiz Maker Logo Make your own Quiz
Powered by: Quiz Maker