Download 70-463 Study Guides 2021

NEW QUESTION 1
You are developing a project that contains multiple SQL Server Integration Services (SSIS) packages. The packages will be deployed to the SSIS catalog. One of the steps in each package accesses an FTP site to download sales transaction data.
You create project parameters to store the username and password that are used to access the FTP site. You need to ensure that the username and password values are encrypted when they are deployed. What should you do?

• A. Set the Sensitive property of the parameters to True.
• B. Set the ProtectionLevel property of the package to EncryptSensitiveWithUserKey.
• C. Change the parameters to package parameters.
• D. Change the project to the Legacy Deployment mode

Answer: A

NEW QUESTION 2
You are designing a complex SQL Server Integration Services (SSIS) project that uses the Project Deployment model.
The project will contain between 15 and 20 packages. All the packages must connect to the same data source and destination.
You need to define and reuse the connection managers in all the packages by using the least development effort.
What should you do?

• A. Copy and paste the connection manager details into each package.
• B. Implement project connection managers.
• C. Implement package connection managers.
• D. Implement parent package variables in all package

Answer: B

NEW QUESTION 3
You are developing a SQL Server Integration Services (SSIS) package.
The package is stored as the myPackage.dtsx file in the root directory of the C drive of the SSIS server. You need to run the package from the command prompt.
Which command should you use?

• A. dtexec /sql myPackage
• B. dtexec /d "C:File SystemmyPackage.dtsx"
• C. dtexec /f "C:myPackage.dtsx"
• D. dtexec /com "myPackage.dtsx"

Answer: C

NEW QUESTION 4
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 2012. Which tool should you use?

• A. SQL Server Integration Services Deployment Wizard
• B. SQL Server Configuration Manager
• C. SSIS Upgrade Wizard in SQL Server Management Studio
• D. SSIS Upgrade Wizard in SQL Server 2008 BIDS

Answer: C

Explanation: You can upgrade packages that were created in earlier versions of Integration Services to the Integration Services format that SQL Server 2012 uses. SQL Server provides the SSIS Package Upgrade Wizard to help in this process. Because you can configure the wizard to backup up your original packages, you can continue to use the original packages if you experience upgrade difficulties.
You can run the SSIS Package Upgrade Wizard from SQL Server Data Tools (SSDT), from SQL Server Management Studio, or at the command prompt.
Note:
* When you upgrade an instance of SQL Server 2005 or SQL Server 2008 to the current release of SQL Server, your existing SQL Server 2008 Integration Services (SSIS) packages are not automatically upgraded to the package format that the current release SQL Server Integration Services uses. You
will have to select an upgrade method and manually upgrade your packages.

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

NEW QUESTION 6
DRAG DROP
You are designing an extract, transform, load (ETL) process with SQL Server Integration Services (SSIS). Two packages, Package A and Package B, will be designed. Package A will execute Package B. Both packages must reference a file path corresponding to an input folder where files will be located for further processing.
You need to design a solution so that the file path can be easily configured with the least administrative and development effort.
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.)

Answer:

Explanation: 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

NEW QUESTION 7
DRAG DROP
You deploy a server that has SQL Server installed.
You deploy a SQL Server Integration Services (SSIS) package to the server.
You need to automate the execution of the package. The solution must ensure that you receive a notification if the package fails to execute.
In which order should you perform all the actions? To answer, move all of the actions to the answer area and arrange them in the correct order.
NOTE: More than one order of answer choices is correct. You will receive credit for any of the correct orders you select.

Answer:

Explanation:
Here are the steps: References:
http://www.sherweb.com/blog/how-to-set-up-database-mail-for-sql-server-job-failures/

NEW QUESTION 8
CORRECT TEXT
You are designing a package control flow. The package moves sales order data from a SQL Azure transactional database to an on-premise reporting database. The package will run several times a day, while new sales orders are being added to the transactional database. The current design of the package control flow is shown in the answer area. (Click the Exhibit button.)

The Insert New Orders Data Flow task must meet the following requirements:
• Usage of the tempdb database should not be impacted.
• Concurrency should be maximized, while only reading committed transactions.
• If the task fails, only that task needs to be rolled back.
You need to configure the Insert New Orders Data Flow task to meet the requirements.
How should you configure the transaction properties? (To answer, select the appropriate setting or settings in the answer area.)

Answer:

Explanation: IsolationLevel = ReadCommited.
TransactionOption = Required
References:
http://msdn.microsoft.com/en-us/library/ms137690.aspx http://msdn.microsoft.com/en-us/library/ms137749.aspx http://msdn.microsoft.com/enus/ library/microsoft.sqlserver.dts.runtime.dtscontainer.isolationlevel.aspx http://msdn.microsoft.com/en-us/library/ms173763.aspx

NEW QUESTION 9
You are troubleshooting an existing SQL Server Integration Services (SSIS) package. On several
occasions, the package execution does not finish and no data seems to have been transferred. You need to ensure that package logging occurs. Your solution must minimize deployment and development efforts.
What should you do?

• A. Run the dtutil command to deploy the package to the SSIS catalog and store the configuration in SQL Server.
• B. Use an msi file to deploy the package on the server.
• C. Open a command prompt and execute the package by using the SQL Log provider and running the dtexecui.exe utility.
• D. Create a reusable custom logging component and use it in the SSIS project.
• E. Open a command prompt and run the dtutil /copy command.
• F. Configure the SSIS solution to use the Project Deployment Model.
• G. Add an OnError event handler to the SSIS project.
• H. Open a command prompt and run the gacutil command.
• I. Open a command prompt and run the dtexec /dumperror /conn command.
• J. Open a command prompt and run the dtexec /rep /conn command.
• K. Configure the output of a component in the package data flow to use a data ta

Answer: C

Explanation: References:
http://msdn.microsoft.com/en-us/library/ms141212.aspx http://www.mssqltips.com/sqlservertip/2450/ssis-package-deployment-model-in-sql-server-2012- part-1-of-2/
http://www.mssqltips.com/sqlservertip/2450/ssis-package-deployment-model-in-sql-server-2012- part-2-of-2/

NEW QUESTION 10
A SQL Server Integration Services (SSIS) package on a computer is failing periodically in production. The package was created less than one year ago and was deployed to the SSIS catalog.
Sometimes the package is started on a SQL Agent schedule; sometimes the package is started manually by an SSIS developer by using the Object Explorer in SQL Server Management Studio. You need to identify the authenticated user responsible for starting the package each time it failed in the past.
Where can you find this information?

• A. the SQL Server Log
• B. the SSISDB.[catalog].[executions] view
• C. the SSISDB.[catalog].[event_messages] view
• D. the SQL Agent Job History
• E. the SQL Agent Error Log

Answer: B

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

NEW QUESTION 12
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: BD

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

NEW QUESTION 13
You develop a SQL Server Integration Services (SSIS) project by using the Package Deployment Model. A package in the project extracts data from a Windows Azure SQL Database database. The package is deployed to SQL Server.
The package is not producing the desired results.
You need to generate the .mdmp and .tmp debug files in order to troubleshoot the issues. What should you do?

• A. Execute the catalog.add_data_tap stored procedure with the package execution_id.
• B. Execute the catalog.create_execution_dump stored procedure with the package execution_id.
• C. Run the DTEXEC utility with the /Logger option.
• D. Run the DTEXEC utility with the /Reporting V option.

Answer: C

NEW QUESTION 14
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 that uses the SQL Server change data capture (CDC) feature.
You have added a CDC Source component to the data flow to read changed data from the source system.
You need to add a data flow transformation to redirect rows for separate processing of insert, update, and delete operations.
Which data flow transformation should you use?

• A. Audit
• B. DQS Cleansing
• C. CDC Splitter
• D. Pivot

Answer: C

Explanation: The CDC splitter splits a single flow of change rows from a CDC source data flow into different data flows for Insert, Update and Delete operations
Ref: http://msdn.microsoft.com/en-us/library/hh758656.aspx

NEW QUESTION 15
Some of the data your company processes is sent in from partners via email.
How would you configure an SMTP connection manager to extract files from email messages?

• A. In the SMTP connection manager, configure the OperationMode setting to Send And Receive.
• B. It is not possible to use the SMTP connection manager in this way, because it can only be used by SSIS to send email messages.
• C. The SMTP connection manager supports sending and receiving email messages by default, so no additional configuration is necessary.
• D. It is not possible to use the SMTP connection manager for this; use the IMAP (Internet MessageAccess Protocol) connection manager instea

Answer: B

Explanation: References:
https://docs.microsoft.com/en-us/sql/integration-services/connection-manager/smtp-connectionmanager

NEW QUESTION 16
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 17
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. Create a Subscription View in MDS.
• B. Access the product entity tables in the MDS database directly.
• C. Use SQL Server Integration Services (SSIS) to extract the data and put it in a staging database.
• D. Use change data capture on the product entity table

Answer: A

NEW QUESTION 18
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 Edito

Answer: B

NEW QUESTION 19
DRAG DROP
You have a SQL Server data warehouse that contains all of the customer data for your company. You plan to deploy an archiving solution.
You need to move a table named OLDCUSTOMERS to a new storage device. OLDCUSTOMERS contains a clustered index.
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.
NOTE: More than one order of answer choices is correct. You will receive credit for any of the correct orders you select.

Answer:

Explanation:

NEW QUESTION 20
HOTSPOT
You have a SQL Server Integration Services (SSIS) package that uses an Execute SQL task to load data to a data warehouse.
You need to log all of the statements that execute for the data warehouse. The solution must store the log on a server that has SQL Server installed.
Which SSIS log provider and event type should you use? To answer, select the appropriate options in the answer area.

Answer:

Explanation:
SSIS log provider: SQL Server
Event type: ExecutesSQLExecutingQuery
The following describes the custom log entry for the Execute SQL task: ExecuteSQLExecutingQuery provides information about the execution phases of the SQL statement. Log entries are written when the task acquires connection to the database, when the task starts to prepare the SQL statement, and after the execution of the SQL statement is completed. The log entry for the prepare phase includes the SQL statement that the task uses.
Note: You can log the calls that the Execute SQL task makes to external data providers. You can use this logging capability to troubleshoot the SQL commands that the Execute SQL task runs. To log the calls that the Execute SQL task makes to external data providers, enable package logging and select the Diagnostic event at the package level.