You are developing a SQL Server Integration Services (SSIS) package to load data into a
Windows Azure SQL Database database. The package consists of several data flow tasks.
The package has the following auditing requirements:
If a data flow task fails, a Transact-SQL (T-SQL) script must be executed.
The T-SQL script must be executed only once per data flow task that fails, regardless of the
nature of the error.
You need to ensure that auditing is configured to meet these requirements.
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.va!idate_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.
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.
Could anybody explain why do you use one event handler for the task and not for each DataFlowTask? And what is the difference between OnError and OnTaskFailed in this context?
I think the key here is ” executed only once per data flow task that fails”
OnTaskFailed
This event is generated only once when a Task “failure”, independently of failure occurrence be in the Package context or just in a Task specific.
thumbs up!!
‘OnError for the package’ would cause the ERROR event to be fired if an error occurs in any other part of the package (irrespective of a Data Flow Task error).
Using ‘OnError for each data flow task’ would not be optimal, and could actually lead to bottlenecks in execution flow.
As sqlninja said, the KEYWORD is important… execute only once per data flow task failure.
isn’t “Use an event handler for OnError for each data flow task” true for “execute only once per data flow task failure.”? Wouldn’t “Use an event handler for OnTaskFailed for the package” only report an error once for the entire package (rather than the required once per data flow task)?
From Wrox 2014 book, OnTaskFailed event = “It’s possible for a task or container to fail without actual errors. You can trap that condition with this event.”
In the question, it is asked, “regardless of the nature of the error.”.
Hence, option C (OnTaskFailed) is the correct answer.