NEW QUESTION 1
You are the data steward for a Business Intelligence project.
You must identify duplicate rows stored in a SQL Server table and output discoveries to a CSV file. A Data Quality Services (DQS) knowledge base has been created to support this project.
You need to produce the CSV file with the least amount of development effort. What should you do?

• A. Create an Integration Services package and use a Data Profiling transform.
• B. Create a custom .NET application based on the Knowledgebase class.
• C. Create a data quality project.
• D. Create a CLR stored procedure based on the Knowledgebase class.
• E. Create a Master Data Services (MDS) business rule.

Answer: C

Explanation: Reference:
http://msdn.microsoft.com/en-us/library/hh213052.aspx

NEW QUESTION 2
HOTSPOT
You are developing a SQL Server Integration Service (SSIS) package. The package loads a customer dimension table by using a data flow task.
Changes to the customer attributes must be tracked over time.
You need to produce a checksum value to identify the rows that have changed since the last Extract, Transform and Load (ETL) process execution. You need to use the least amount of development effort to achieve this goal.
Which transformation should you use? (To answer, select the appropriate transformation in the answer area.)

Answer:

Explanation:

NEW QUESTION 3
You are developing a SQL Server Integration Services (SSIS) package to load data into a data warehouse.

You need to establish the correct order for loading each table to maximize parallel processing. Which order should you use?
A.
1. DimCurrency, DimScenario, DimAccount in parallel
2. DimOrganization
3. FactFinance
B.

• A. DimCurrency, DimOrganization in parallel2. DimScenario, DimAccount in parallel3. FactFinance C.1. DimCurrency, FactFinance in parallel2. DimOrganization, DimScenario, DimAccount in parallel D.1. FactFinance2. DimOrganization, DimScenario, DimAccount in parallel3. DimCurrency E.1. DimCurrency2. DimOrganization3. DimScenario, DimAccount in parallel4. FactFinance

Answer: A

Explanation: References:
http://msdn.microsoft.com/en-us/library/ms139892.aspx http://msdn.microsoft.com/en-us/library/ms141261.aspx

NEW QUESTION 4
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?

• A. Me.Dts.Connections(“File1”).AcquireConnection(“C:temptemp.txt”)
• B. Me.Dts.Connections(“File1”).Variables.Item[“FileName”].value = “C:temptemp.txt”
• C. Me.Dts.Connections(“File1”).Properties.Item[“FileName”].Setvalue = “C:temptemp.txt”
• D. Me.Dts.Connections(“File1”).ConnectionString = “C:temptemp.txt”

Answer: B

Explanation: References:
https://social.msdn.microsoft.com/Forums/sqlserver/en-US/faeb29c7-6879-4d51-b94e- 09404de02339/how-to-pass-the-existing-filename-into-a-variable-in-ssis-scripttask? forum=sqlintegrationservices

NEW QUESTION 5
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

NEW QUESTION 6
You are writing a SQL Server Integration Services (SSIS) package that transfers data from a legacy system.
Data integrity in the legacy system is very poor. Invalid rows are discarded by the package but must be logged to a CSV file for auditing purposes.
You need to establish the best technique to log these invalid rows while minimizing the amount of development effort.
What should you do?

• A. Add a data tap on the output of a component in the package data flow.
• B. Deploy the package by using an msi file.
• C. Run the package by using the dtexecui.exe utility and the SQL Log provider.
• D. uses the dtutil /copy command.
• E. Deploy the package to the Integration Services catalog by using dtutil and use SQL Server to store the configuration.
• F. Create an OnError event handler.
• G. uses the Project Deployment Wizard.
• H. Use the gacutil command.
• I. Create a reusable custom logging component.
• J. Run the package by using the dtexec /rep /conn command.
• K. Run the package by using the dtexec /dumperror /conn command.

Answer: A

Explanation: Reference:
http://www.rafael-salas.com/2012/01/ssis-2012-quick-peek-to-data-taps.html http://msdn.microsoft.com/en-us/library/hh230989.aspx http://msdn.microsoft.com/en-us/library/jj655339.aspx

NEW QUESTION 7
You develop and deploy a SQL Server Integration Services (SSIS) package. The package is stored in the file system.
You need to execute the package without importing it to the SSIS server.
What should you use to execute the package? (Each correct answer presents a complete solution. Choose all that apply.)

• A. catalog.start_package
• B. dtexec
• C. SQL Server Management Studio
• D. SQL Server Agent

Answer: BD

Explanation: Ref: http://technet.microsoft.com/en-us/library/ms141708(v=sql.110).aspx

NEW QUESTION 8
DRAG DROP
You are developing a SQL Server Integration Services (SSIS) package that is ready for deployment to a production server. The package contains sensitive information secured by using the EncryptSensitiveWithUserKey package protection level.
You are preparing the package for deployment by the production operations team.
You need to ensure that the production operations team can open and execute the package without re-entering the sensitive information.
Which three steps 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:

Explanation:

NEW QUESTION 9
DRAG DROP
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:

Explanation: Ref: http://msdn.microsoft.com/en-gb/library/hh230989(v=sql.110).aspx

NEW QUESTION 10
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.)

Answer:

Explanation: References:
http://msdn.microsoft.com/en-us/library/ms162802.aspx http://msdn.microsoft.com/en-us/library/ms188365.aspx http://msdn.microsoft.com/en-us/library/ms175937.aspx http://msdn.microsoft.com/en-us/library/aa337544.aspx

NEW QUESTION 11
You plan to deploy a package to a server that has SQL Server installed. The server contains a SQL Server Integration Services (SSIS) catalog.
You need to ensure that the package credentials are encrypted. Which protection level should you set for the package?

• A. ServerStorage
• B. EncryptAllWithUserKey
• C. EncryptSensitivewithPassword
• D. EncryptSensitivewithUserKey

Answer: D

Explanation: The SSISDB catalog uses the ServerStorage protection level. When you deploy an Integration Services project to the Integration Services server, the catalog automatically encrypts the package data and sensitive values. The catalog also automatically decrypts the data when you retrieve it.
If you export the project (.ispac file) from the Integration Services server to the file system, the system automatically changes the protection level to EncryptSensitiveWithUserKey. References:
https://docs.microsoft.com/en-us/sql/integration-services/security/access-control-for-sensitivedata- in-packages?view=sql-server-2021

NEW QUESTION 12
HOTSPOT
You are editing a SQL Server Integration Services (SSIS) project named Project1 in SQL Server Data Tools.
A package Connection Manager has been parameterized with project scope.
You need to display the parameters that have been generated for the Connection Manager. What should you use? To answer, select the appropriate setting or settings in the answer area.

Answer:

Explanation:

NEW QUESTION 13
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. Pivot
• C. Multicast
• D. Lookup

Answer: A

NEW QUESTION 14
You have a SQL Server Integration Services (SSIS) package that is configured as shown in the exhibit. (Click the Exhibit button.)

You need to recommend changes to the package to reduce load times.
Which two changes should you recommend? Each correct answer presents part of the solution.

• A. Remove the OLE DB command and use a Script task.
• B. Add an Execute SQL task that batch-updates changed rows.
• C. Set the ValidateExternalMetadata property to False.
• D. Add an Execute SQL task that truncates and reloads the table.
• E. Remove the OLE DB command and use an OLE DB destinatio

Answer: BE

Explanation: If the OLE DB destination uses a fast-load data access mode, you can specify the following fast load options in the user interface, OLE DB Destination Editor, for the destination:
References:
https://docs.microsoft.com/en-us/sql/integration-services/data-flow/ole-db-destination?view=sqlserver- 2021#fast-load-options

NEW QUESTION 15
You manage a SQL Server Master Data Services (MDS) environment.
A new application requires access to the product data that is available in the MDS repository. You need to design a solution that gives the application access to the product data with the least amount of development effort.
What should you do?

• A. Use sp_addlinkedserver to add a linked server to access the MDS database tables directly.
• B. Create a Subscription View in MDS.
• C. Use transactional replication for data synchronization.
• D. Create an OLE DB connection string that sets the Provider property to MD

Answer: B

Explanation: You create a subscription view to export Master Data Services data to a subscribing system such as SQL Server. You use the subscribing system to view the data in the Master Data Services database. References:
https://docs.microsoft.com/en-us/sql/master-data-services/overview-exporting-data-master-dataservices? view=sql-server-2021

NEW QUESTION 16
DRAG DROP
You are loading a dataset into SQL Server. The dataset contains numerous duplicates for the Artist and Song columns.
The values in the Artist column in the dataset must exactly match the values in the Artist domain in the knowledge base. The values in the Song column in the dataset can be a close match with the values in the Song domain.
You need to use SQL Server Data Quality Services (DQS) to define a matching policy rule to identify duplicates.
How should you configure the Rule Editor? (To answer, drag the appropriate answers to the answer area.)

Answer:

Explanation:

NEW QUESTION 17
You are implementing a SQL Server Integration Services (SSIS) 2012 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 date stored in a control table of the Windows Azure SQL Database database 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?

• A. an Expression task
• B. an Execute SQL task
• C. a Script component
• D. a Script task

Answer: B

NEW QUESTION 18
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 sources should you use? Select Two

• A. Raw File source
• B. XML source
• C. Custom source component
• D. Flat File source
• E. script component

Answer: CE