Q1. 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
Q2. You are editing a SQL Server Integration Services (SSIS) package that contains a task with a sensitive property.
You need to create a project parameter and configure it so that its value is encrypted when it is deployed to the SSIS catalog.
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:
Q3. You administer a SQL Server Integration Services (SSIS) solution in the SSIS catalog. A SQL Server Agent job is used to execute a package daily with the basic logging level.
Recently, the package execution failed because of a primary key violation when the package inserted data into the destination table.
You need to identify all previous times that the package execution failed because of a primary key violation.
What should you do?
A. Use an event handler for OnError for the package.
B. Use an event handler for OnError for each data flow task.
C. Use an event handler for OnTaskFailed for the package.
D. View the job history for the SQL Server Agent job.
E. View the All Messages subsection of the All Executions report for the package.
F. Store the System::SourceID variable in the custom log table.
G. Store the System::ServerExecutionID variable in the custom log table.
H. Store the System::ExecutionInstanceGUID variable in the custom log table.
I. Enable the SSIS log provider for SQL Server for OnError in the package control flow.
J. Enable the SSIS log provider for SQL Server for OnTaskFailed in the package control flow.
K. Deploy the project by using dtutil.exe with the /COPY DTS option.
L. Deploy the project by using dtutil.exe with the /COPY SQL option.
M. Deploy the .ispac file by using the Integration Services Deployment Wizard.
N. Create a SQL Server Agent job to execute the SSISDB.catalog.validate_project stored procedure.
O. Create a SQL Server Agent job to execute the SSISDB.catalog.validate_package stored procedure.
P. Create a SQL Server Agent job to execute the
SSISDB.catalog.create_execution and SSISDB.catalog.start_execution stored procedures.
Q. Create a table to store error information. Create an error output on each data flow
destination that writes OnError event text to the table.
R. Create a table to store error information. Create an error output on each data flow
destination that writes OnTaskFailed event text to the table.
Answer: E
Q4. 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 uti
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 tap.
Answer: G
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/
Q5. 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
Answer: C
Q6. 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: B,D
Q7. You are using a SQL Server Integration Services (SSIS) project that is stored in the SSIS catalog. An Environment has been defined in the SSIS catalog.
You need to add the Environment to the project.
Which stored procedure should you use?
A. catalog.set_environment_reference_type
B. catalog.set_environment_property
C. catalog.create_environment_reference
D. catalog.create_environment
Answer: C
Q8. 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.)
Answer:
Q9. You are completing the installation of the Data Quality Server component of SQL Server Data Quality Services (DQS).
You need to complete the post-installation configuration.
What should you do?
A. Run the DQSInstaller.exe command.
B. Install the data providers that are used for data refresh.
C. Install ADOMD.NET.
D. Run the dbimpexp.exe command.
Answer: A
Explanation: References: http://msdn.microsoft.com/en-us/library/ff877917.aspx
http://msdn.microsoft.com/en-us/library/gg492277.aspx
Q10. You are building a SQL Server Integration Services (SSIS) package to load data from all files that are automatically copied to a directory each night through an external FTP process.
You need to load data from all copied files to a destination table in SQL Server.
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: