You are designing a SQL Server Integration Services(SSIS) solution. The solution will contain an SSISproject
that includes several SSIS packages. Each SSIS package will define the same connection managers and
variables. You have the following requirements:
Ensure that the deployment model supports changing the content of connection strings by using parameters
at execution time.
Ensure that the deployment model automatically starts from calls to the catalog.start_execution stored
procedure in the SSISDB database.
Maximize performance at execution time.
Minimize development effort.
You need to design a solution that meets the requirements.
What should you do? (More than one answer choice may achieve the goal. Select the BEST answer.)

You are designing an extract, transform, load (ETL)process for loading data from a SQL Server database into
a large fact table in a data warehouse each day with the prior day’s sales data. The ETL process for the fact
table must meet the following requirements:
Load new data in the shortest possible time.
Remove data that is more than 36 months old.
Ensure that data loads correctly.
Minimize record locking.
Minimize impact on the transaction log.
You need to design an ETL process that meets the requirements. What should you do? (More than one answer
choice may achieve the goal. Select the BEST answer.)

You are designing a fact table in a SQL Server database. The fact table must meet the following requirements:
Include a columnstore index.
Allow users to choose up to 10 dimension tables and up to five facts at one time.
Maximize performance of queries that aggregate measures by using any of the 10 dimensions.
Support billions of rows.
Use the most efficient design strategy.
You need to design the fact table to meet the requirements. What should you do? (More than one answer
choice may achieve the goal. Select the BEST answer.)

You are designing a SQL Server Integration Services(SSIS) solution that will load multiple Online Transactional
Processing (OLTP) data sources into a SQL Server data mart. You have the following requirements:
Ensure that the process supports the creation of an exception report that details possible duplicatekey values,
null ratios within columns, and column-length distributions of values.
Ensure that users can generate the exception report in an XML format.
Use the minimum development effort.
You need to design the SSIS solution to meet the requirements. What should you do? (More than one answer
choice may achieve the goal. Select the BEST answer.)

You are designing a SQL Server Integration Services(SSIS) solution. The solution will contain an SSISproject
that includes several SSIS packages. Each SSIS package will define the same connection managers and
variables. You have the following requirements:
The deployment model must support changing the content of connection strings by using parameters at
execution time.
The deployment model must automatically log events to the SSISOB database.
Maximize performance at execution time.
You need to design a solution that meets the requirements. What should you do? (More than one answer
choice may achieve the goal. Select the BEST answer.)

You are designing a partitioning strategy for a large fact table in a data warehouse. Tens of millionsof new
records are loaded into the data warehouse weekly, outside of business hours. Most queries are generated by
reports and by cube processing. Data is frequently queried at the day level and occasionally at the month level.
You need to partition the table to maximize the performance of queries. What should you do? (More thanone
answer choice may achieve the goal. Select the BESTanswer.)

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? (To answer, move the
appropriate actions from the list of actions to theanswer area and arrange them in the correct order.)

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? (To answer, drag the appropriate transformation from the list
of options to the correct location or locations in the answer area.)

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

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?
To answer, select the appropriate option from the drop-down list in the dialog box.