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…

Question

Options

• AUse an event handler for OnError for the package.
• BUse an event handler for OnError for each data flow task.
• CUse an event handler for OnTaskFailed for the package.
• DView the job history for the SQL Server Agent job.
• EView the All Messages subsection of the All Executions report for the package.
• FStore the System::SourceID variable in the custom log table.
• GStore the System::ServerExecutionID variable in the custom log table.
• HStore the System::ExecutionInstanceGUID variable in the custom log table.
• IEnable the SSIS log provider for SQL Server for OnError in the package control flow.
• JEnable the SSIS log provider for SQL Server for OnTaskFailed in the package control flow.

• A
  5% (2)
• C
  2% (1)
• D
  2% (1)
• G
  10% (4)
• I
  81% (34)

Explanation

Enabling the SSIS log provider for SQL Server with the OnError event at the package control flow level captures every error event raised by any task or component anywhere in the package and writes each event record to a SQL Server table (sysssislog) via the specified OLE DB connection manager. This single configuration covers all data flow tasks without modifying each one individually. Answer A (OnError event handler on the package) could also capture errors but requires writing custom script logic to direct output to a table; it is more complex. Answer B (OnError per task) requires individual configuration on each data flow task, violating least-effort. OnTaskFailed (C, J) fires only once when a task exits in a failed state - it does not capture individual error messages generated within the task.

No community discussion yet for this question.