Get Full Version of the Exam
You have a data warehouse that is hosted in a SQL Server instance. The data in the data warehouse is loaded by running bcp.exe You discover that the transaction logs regularly fill up the local hard disk on the server and that a WRITELOG wait is present. You need to reduce the amount of disk space used to store the transaction logs and to remove the WRI7TLOG wait.
Which two database property settings should you modify?
To answer, select the appropriate settings in the answer area.
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?
To answer, select the appropriate option in the answer area.
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?
Join the two fact tables.
Merge the fact tables.
Change the level of granularity in both fact tables to be the same.
Partition the fact tables by day.
Correct Answer: D
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.
Correct Answer: C
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 four actions should you perform in sequence?
To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct order.
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
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 ConnectionName.
Set the Required property of the parameter to True.
Correct Answer: CEF
C: From question: quot; The server name for the database must be set by using a parameter named ParamConnection when any package in the project is executed.quot;
E: SSIS 2016 has introduced the concept of Project level connection managers. An SSIS project is generally more than one package. To simplify lives, the SSIS team now allows for the sharing of common resources across projects, connection managers being one of those resources.
F: When a parameter is marked as required, a server value or execution value must be specified for that parameter. Otherwise, the corresponding package does not execute. Although the parameter has a default value at design time, it will never be used once the project is deployed.
Integration Services (SSIS) parameters allow you to assign values to properties within packages at the time of package execution. You can create project parameters at the project level and package parameters at the package level. Project parameters are used to supply any external input the project receives to one or more packages in the project. Package parameters allow you to modify package execution without having to edit and redeploy the package.
Reference: Integration Services (SSIS) Parameters
You are implementing a new SQL Server Integration Services (SSIS) 2016 package that loads data from various flat files and a Windows Azure SQL Database database.
Daily transactions must be loaded into a staging database. All the SSIS tasks will use the CurrentDate variable as the transaction date.
You need to set the CurrentDate variable to the current execution date when the package starts. You need to achieve this goal by using the least amount of development effort.
What should you use to set the variable?
An Expression task
A Script component
A Script task
An Execute SQL task
Correct Answer: A
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
Custom source component
Flat File source
Correct Answer: C
You have a database named Sales and a data warehouse named DataW.
From Sales, you plan to bulk insert to a table in DataW that is more than 2TB. The process will insert a minimum of 11 million rows simultaneously.
You need to identify which data storage strategy must be used to minimize load times. Which data storage strategy should you identify?
a Clustered Columnstore
a file table.
a durable In-Memory OLTP
a page-compressed heap
Correct Answer: A
You are developing a SQL Server Integration Services (SSIS) package. You create a script task in the package.
You need to dynamically set the file name of a connection object named File1 by using the script task
Which line of code should you add to the script task?
Me.Dts.Connections(quot;File1quot;).Variables.Item[quot;FileNamequot;].value = quot;C:\temp\temp.txtquot;
Me.Dts.Connections(quot;File1quot;).Properties.Item[quot;FileNamequot;].Setvalue = quot;C:\temp\temp.txtquot;
Me.Dts.Connections(quot;File1quot;).ConnectionString = quot;C:\temp\temp.txtquot;