You are developing a SQL Server Integration Services (SSIS) package to load data into a
data warehouse. The package consists of several data flow tasks.
The package experiences intermittent errors in the data flow tasks.
If any data flow task fails, all package error information must be captured and written to a
SQL Server table by using an OLE DB connection manager.
You need to ensure that the package error information is captured and written to the table.
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.
j?
Thats what I think. J.
You guys got it. It’s J.
I think answer Q or R because of the part : “and written to a SQL Server table” in the question. But I am not sure between OnError or OnTaskFailed.
I think it is I. the original answer is correct.
https://msdn.microsoft.com/en-us/library/ms140246(v=sql.110).aspx
DTS.LogProviderSQLServer uses OLEDB connection manager
I think the correct answer is;
R
R
Correct answer is J: Enable SSIS Log Provider for SQL Server for ‘OnTaskFailed’ event.
Because of the requirement ‘all package error information must be captured and written to… using an OLE DB connection manager.’ DTS.LogProviderSQLServer uses OLE conn mgr.
However, option I is not looking for “intermittent errors in the data flow tasks”. Options Q and R are too laborious.
+7
Since Q & R is at the task level and not the package level, I don’ tthink it will provide the necessary package info requested in the question when it says “the package error information” as if it was obvious what “the” information is. J definitely works though:
https://msdn.microsoft.com/en-us/library/ms141212.aspx