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. .dtsConfig file
B. Registry Entry
C. Environment Variable
D. Parent Package Variable
E. XML Configuration File
Answer: C
Explanation:
Reference:
http://msdn.microsoft.com/en-us/library/ms345179.aspx http://msdn.microsoft.com/en-us/library/ms141708.aspx http://msdn.microsoft.com/en-us/library/hh213214.aspx http://msdn.microsoft.com/en-us/library/hh213296.aspx http://msdn.microsoft.com/en-us/library/hh213293.aspx
Q2. DRAG DROP
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? (To answer, drag the appropriate transformation from the list of answer options to the correct location in the answer area.)
Answer:
Explanation:
Records that have no match in the destination are new records and so should be inserted. Any errors should be recorded in the flat file.
References:
http://msdn.microsoft.com/en-us/library/ms141821.aspx http://msdn.microsoft.com/en-us/library/bb895366.aspx
https://www.simple-talk.com/sql/ssis/implementing-lookup-logic-in-sql-server-integration- services/
Q3. DRAG DROP
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? (To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct order.)
Answer:
Q4. You administer a Microsoft SQL Server 2021 database. The database contains a table named Employee. Part of the Employee table is shown in the exhibit.
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 create a unique constraint?
A. DateHired
B. DepartmentID
C. EmployeelD
D. EmployeeNum
E. FirstName
F. JobTitle
G. LastName
H. MiddleName
I. ReportsToID
Answer: D
Q5. 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?
A. Use the Expression task.
B. Use the Script task.
C. Develop a custom task.
D. Use the Script component,
E. Develop a custom component.
Answer: C
Explanation: References:
http://msdn.microsoft.com/en-us/library/ms135965.aspx http://msdn.microsoft.com/en-us/library/ms345161.aspx
Q6. You are developing a SQL Server Integration Services (SSIS) package to load data into a SQL Server table on ServerA. The package includes a data flow and is executed on ServerB. The destination table has its own identity column.
The destination data load has the following requirements:
✑ The identity values from the source table must be used.
✑ Default constraints on the destination table must be ignored.
✑ Batch size must be 100,000 rows.
You need to add a destination and configure it to meet the requirements. Which destination should you use?
A. OLE DB Destination with Fast Load
B. SQL Server Destination
C. ADO NET Destination without Bulk Insert
D. ADO NET Destination with Bulk Insert
E. OLE DB Destination without Fast Load
Answer: A
Explanation:
Reference: http://msdn.microsoft.com/en-us/library/ms141237.aspx
Reference: http://msdn.microsoft.com/en-us/library/ms139821.aspx
Reference: http://msdn.microsoft.com/en-us/library/ms141095.aspx
Q7. You are designing an enterprise star schema that will consolidate data from three independent data marts. One of the data marts is hosted on SQL Azure.
Most of the dimensions have the same structure and content. However, the geography dimension is slightly different in each data mart.
You need to design a consolidated dimensional structure that will be easy to maintain while ensuring that all dimensional data from the three original solutions is represented.
What should you do?
A. Create a junk dimension for the geography dimension.
B. Implement change data capture.
C. Create a conformed dimension for the geography dimension.
D. Create three geography dimensions.
Answer: C
Q8. You are reviewing the design of a student dimension table in an existing data warehouse hosted on SQL Azure.
The current dimension design does not allow the retention of historical changes to student attributes such as ParentOccupation.
You need to redesign the dimension to enable the full historical reporting of changes to multiple student attributes including ParentOccupation.
What should you do?
A. Add CurrentValue and PreviousValue columns to the student dimension.
B. Enable Snapshot Isolation on the data warehouse.
C. Add an IsCurrent column to the student dimension.
D. Add StartDate and EndDate columns to the student dimension.
Answer: D
Explanation: Adding a start and end date will give you this ability as when a record is inserted and given a start and end date, you’ll have the ability to determine when they were active therefore giving you a retention of historical changes
Q9. You are creating a SQL Server Master Data Services (MDS) model for a company.
The source data for the company is stored in a single table that contains the manager-to- subordinate relationships.
You need to create a hierarchy representing the organizational structure of the company. Which hierarchy type should you use?
A. Organizational
B. Recursive
C. Non-Mandatory Explicit
D. Many-to-Many
Answer: B
Q10. To facilitate the troubleshooting of SQL Server Integration Services (SSIS) packages, a logging methodology is put in place.
The methodology has the following requirements:
•The deployment process must be simplified.
•All the logs must be centralized in SQL Server.
•Log data must be available via reports or T-SQL.
•Log archival must be automated.
You need to configure a logging methodology that meets the requirements while minimizing the amount of deployment and development effort.
What should you do?
A. Open a command prompt and run the gacutil command.
B. Open a command prompt and execute the package by using the SQL Log provider and running the dtexecui.exe utility.
C. Add an OnError event handler to the SSIS project.
D. Use an msi file to deploy the package on the server.
E. Configure the output of a component in the package data flow to use a data tap.
F. Run the dtutil command to deploy the package to the SSIS catalog and store the configuration in SQL Server.
G. Open a command prompt and run the dtexec /rep /conn command.
H. Open a command prompt and run the dtutil /copy command.
I. Open a command prompt and run the dtexec /dumperror /conn command.
J. Configure the SSIS solution to use the Project Deployment Model.
K. Create a reusable custom logging component and use it in the SSIS project.
Answer: B
Explanation:
Reference:
http://msdn.microsoft.com/en-us/library/ms140246.aspx http://msdn.microsoft.com/en-us/library/ms180378(v=sql.110).aspx