You are developing a SQL Server Integration Services (SSIS) project by using the Project
Deployment Model. All packages in the project must log custom messages.
You need to produce reports that combine the custom log messages with the systemgenerated log messages. 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.
project deployment writes custom logging to ssisdb catalog. As far as I know there is no such thing as a custom log table.
Not sure but I believe it should be N
Why not Q?
I think possibly answer is Q, that is what I would do.
If we assumed G was the correct answer:
https://msdn.microsoft.com/en-us/library/ms141788.aspx
The default value is zero. The value is changed only if the package is executed by ISServerExec on the Integration Services Server. When there is a child package, the value is passed from the parent package to child package.
This might be useful to identify that the errors was created by ISServerExec allowing us to report against custom messages vs ISServerExec?
But then again, the question doesn’t specifically ask this.
G it is: http://dba.stackexchange.com/questions/38808/relating-executioninstanceguid-to-the-ssisdb