Q1. You are developing a SQL Server Integration Services (SSIS) package that imports data into a data warehouse hosted on SQL Azure.

The package uses a Foreach container to process text files found in a folder. The package must be deployed to a single server by using the Project Deployment model.

Multiple SQL Server Agent jobs call the package. Each job is executed on a different schedule.

Each job passes a different folder path to the package.

You need to configure the package to accept the folder path from each job. Which package configuration should you use?

A. Parent Package Variable

B. XML Configuration File

C. Environment Variable

D. .dtsConfig file

E. Registry Entry

View Answer

Q2. 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 2021. Which tool should you use?

A. SQL Server dtexec utility (dtexec.exe)

B. SQL Server DTExecUI utility (dtexecui.exe)

C. SSIS Upgrade Wizard in SQL Server Data Tools

D. SQL Server Integration Services Deployment Wizard

View Answer

Q3. HOTSPOT

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?

To answer, select the appropriate page in the answer area.

View Answer

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

A. Partition the destination fact table by date. Insert new data directly into the fact table and delete old data directly from the fact table.

B. Partition the destination fact table by date. Use partition switching and staging tables both to remove old data and to load new data.

C. Partition the destination fact table by customer. Use partition switching both to remove old data and to load new data into each partition.

D. Partition the destination fact table by date. Use partition switching and a staging table to remove old data. Insert new data directly into the fact table.

View Answer

Q5. DRAG DROP

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

View Answer

Q6. DRAG DROP

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? (To answer, drag the appropriate data import command or recovery model to the appropriate location or locations in the answer area. Each data import command or recovery model may be used once, more than once, or not at all. You may need to drag the split bar between panes or scroll to view content.)

View Answer

Q7. 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?

A. Create an On Error event handler.

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

C. Use the gacutil command.

D. Create a reusable custom logging component.

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

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

G. Use the Integration Services Deployment Wizard.

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

I. Use the dtutil /copy command.

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

K. Deploy the package by using an msi file.

View Answer

Q8. You are developing a SQL Server Integration Services (SSIS) package that imports data into a data warehouse.

You add an Execute SQL task to the control flow. The task must execute a simple INSERT statement.

The task has the following requirements:

•The INSERT statement must use the value of a string package variable. The variable name is StringVar.

•The Execute SQL task must use an OLE DB Connection Manager.

In the Parameter Mapping tab of the Execute SQL task, StringVar has been added as the only parameter.

You must configure the SQLStatement property of the Execute SQL task. Which SQL statement should you use?

A. INSERT INTO dbo.Table (variablevalue) VALUES (@StringVar)

B. INSERT INTO dbo.Table (variablevalue) VALUES ($Project::StringVar)

C. INSERT INTO dbo.Table (variablevalue) VALUES (?)

D. INSERT INTO dbo.Table (variablevalue) VALUES ($Package::StringVar)

View Answer

Q9. You are a database developer of a Microsoft SQL Server 2021 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?

A. CREATE TABLE Customer (SourceID int NOT NULL IDENTITY,

CustomerID int NOT NULL IDENTITY, CustomerName varchar(255) NOT NULL);

B. CREATE TABLE Customer (SourceID int NOT NULL,

CustomerID int NOT NULL PRIMARY KEY CLUSTERED,

CustomerName varchar(255) NOT NULL);

C. CREATE TABLE Customer

(SourceID int NOT NULL PRIMARY KEY CLUSTERED,

CustomerID int NOT NULL UNIQUE, CustomerName varchar(255) NOT NULL);

D. CREATE TABLE Customer (SourceID int NOT NULL, CustomerID int NOT NULL,

CustomerName varchar(255) NOT NULL,

CONSTRAINT PK_Customer PRIMARY KEY CLUSTERED

(SourceID, CustomerID));

View Answer

Q10. You are developing a SQL Server Integration Services (SSIS) package that imports data from a relational database to a data warehouse.

You are importing data from a relational table named Projects. The table has change data capture enabled on all columns.

You need to process only the most recent values from rows that have been inserted or updated since the previous execution of the package.

Which query should you use as the data source?

A. Option A

B. Option B

C. Option C

D. Option D

View Answer