Introducing The New!
Surepassexam Collection
Get Unlimited Access to all
Surepassexam's PREMIUM files
Q1. 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:
Q2. You are designing a partitioning strategy for a large fact table in a data warehouse. Tens of millions of 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 than one answer choice may achieve the goal. Select the BEST answer.)
A. Partition the fact table by month, and compress each partition.
B. Partition the fact table by week.
C. Partition the fact table by year.
D. Partition the fact table by day, and compress each partition.
Answer: D
Q3. 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 Change Type values should you select? (To answer, drag the appropriate value from the list of values to the correct location or locations in the answer area.)
Answer:
Q4. You are developing a SQL Server Integration Services (SSIS) project by using the Project Deployment Model.
The project is deployed to a single SSIS catalog, and transfers data to and from multiple databases hosted on SQL Server.
The project must be configured to be able to export data to and from five different production servers that run SQL Server 2012. Each target server requires different values for connection strings and parameters in the SSIS project.
You need to meet the requirements by using the least amount of administrative effort.
What should you do?
A. For each target server, create separate registry entry configurations. Select the registry entry at package execution time.
B. For each target server, create separate Environments in the SSIS catalog of the host SQL Server SSIS instance. Select the appropriate Environment at package execution time.
C. Create one SSIS catalog Environment. Change the values of each Environment variable at package execution time.
D. For each target server, create a separate XML configuration file. Select the XML configuration file at package execution time.
Answer: B
Q5. 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.
Answer: G
Reference:
http://msdn.microsoft.com/en-us/library/hh479588.aspx
http://msdn.microsoft.com/en-us/library/hh213290.aspx
http://msdn.microsoft.com/en-us/library/hh213373.aspx
Q6. 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.)
A. Enable CLR Integration.
B. Enable FILESTREAM with Full Access.
C. Enable the Resource Governor.
D. Change the recovery mode of the msdb database to FULL.
E. Change the Server-wide Default Logging Level in SSISDB to Verbose.
F. Start the SQL Server Browser service.
G. Start the SQL Server Agent service.
Answer: A,D
Q7. You are designing a SQL Server Integration Services (SSIS) package that uses the Fuzzy Lookup transformation.
The reference data to be used in the transformation changes with every package execution.
Common language runtime (CLR) integration cannot be enabled on the SQL Server database where the reference table is located.
You need to configure the Fuzzy Lookup transformation in the most efficient manner.
What should you do?
A. Execute the sp_FuzzyLookupTableMaintenanceInvoke stored procedure.
B. Select the GenerateNewIndex option in the Fuzzy Lookup Transformation Editor.
C. Execute the sp_FuzzyLookupTableMaintenanceUninstall stored procedure.
D. Select the GenerateAndPersistNewIndex option in the Fuzzy Lookup Transformation Editor.
Answer: B
Q8. You are administering SQL Server Integration Services (SSIS) permissions on a production server that runs SQL Server 2012.
Package developers in your company must have permission to perform the following tasks only on their own projects:
View projects and packages View Environments Validate packages Execute packages
You need to grant rights to the developers without assigning unnecessary privileges.
What should you do? (Each correct answer presents part of a solution. Choose all that apply.)
A. Add developer logins to the db_ssisltduser role in the msdb database.
B. Add developer logins to the db_ssisoperator role in the msdb database.
C. Grant Execute permission in the projects for the developer logins.
D. Grant Read permission in the SSIS catalog folder, the projects, and the Environments.
E. Add developer logins to the ssis_admin role in the SSISDB database.
F. Grant Modify permission in the projects for the developer logins.
Answer: B,D
Explanation: B: db_ssisoperator
* Read actions Enumerate all packages. View all packages. Execute all packages. Export all packages.
Execute all packages in SQL Server Agent.
* Write actions None
D: Need read permissions on the Environments in order to be able to view them.
Incorrect:
Not A: db_ssisltduser
* Read actions
Enumerate own packages.
Enumerate all packages.
View own packages.
Execute own packages.
Export own packages.
* Write Actions
Import packages.
Delete own packages.
Change own package roles.
Not E: db_ssisadmin
Too many permissions (such as delete all packages).
Q9. You are deploying a new SQL Server Integration Services (SSIS) package to several servers.
The package must meet the following requirements:
. .NET Common Language Runtime (CLR) integration in SQL Server must not be enabled.
. The Connection Managers used in the package must be configurable without editing the package.
. The deployment procedure must be automated as much as possible.
You need to set up a deployment strategy that meets the requirements.
What should you do?
A. Use the gacutil command.
B. Use the dtutil /copy command.
C. Use the Project Deployment Wizard.
D. Create an OnError event handler.
E. Create a reusable custom logging component.
F. Run the package by using the dtexec /rep /conn command.
G. Run the package by using the dtexec /dumperror /conn command.
H. Run the package by using the dtexecui.exe utility and the SQL Log provider.
I. Add a data tap on the output of a component in the package data flow.
J. Deploy the package by using an msi file.
K. Deploy the package to the Integration Services catalog by using dtutil and use SQL Server to store the configuration.
Answer: B
Q10. 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?
A. Raw File source
B. XML source
C. Custom source component
D. Flat File source
Answer: C
Q11. 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
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
Q12. 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? (To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct order.)
Answer:
Q13. 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?
A. Add a column mapping for the Team Type column.
B. Map a composite domain to the source column.
C. Create a composite domain that includes the Team Type column.
D. Add a domain for the Team Type column.
Answer: D
Explanation: References: http://msdn.microsoft.com/en-us/library/ff877917.aspx
http://msdn.microsoft.com/en-us/library/hh213015.aspx http://msdn.microsoft.com/en-us/library/gg524799.aspx
Q14. You are designing a SQL Server Integration Services (SSIS) data flow to load sales
transactions from a source system into a data warehouse hosted on Windows Azure SQL Database. One of the columns in the data source is named ProductCode.
Some of the data to be loaded will reference products that need special processing logic in the data flow.
You need to enable separate processing streams for a subset of rows based on the source product code.
Which Data Flow transformation should you use?
A. Script Component
B. Audit
C. Destination Assistant
D. Data Conversion
Answer: A
Q15. A SQL Server Integration Services (SSIS) 2012 package currently downloads sales data from a Windows Azure SQL Database database.
To improve sales data accuracy, exchange rates must be downloaded daily from a public HTTP website instead of from a weekly flat file. The public website hosts a comma-
separated values (CSV) file that contains one row per currency.
You need to download the CSV file to the environment.
What should you use to retrieve the document from the website?
A. a Script component
B. a Web Service task
C. a Web Service source
D. a Script task
Answer: D
Q16. You are developing a SQL Server Integration Services (SSIS) package to implement an incremental data load strategy.
The package reads data from a source system. Depending on the value in a source column, the package redirects rows to one of five different data flow paths.
You need to add a data flow transformation to support the package redirection.
Which data flow transformation should you use?
A. Conditional Split
B. pjvot
C. Multicast
D. Lookup
Answer: A