Basic Approach to Diagnostic Logging in SSIS

Question

Given help from this microsoft link, I am aware of many tools related to SSIS diagnostics:

1. Event Handlers (in particular, "OnError")
2. Error Outputs
3. Operations Reports
4. SSISDB Views
5. Logging
6. Debug Dump Files

I just want to know what is the basic, "go to" approach for (non-production) diagnostics setup with SSIS. I am a developer who WILL have access to the QA and UAT servers where I will be performing diagnostics.

In my first attempt to find the source of an error, I used SSMS to view operational reports. All I saw was this:

I followed the instructions shown above, but all it did was lead me in a circle. The overview allows me to see the details, but the details show the above message and ask me to go back to the overview. In short, there is zero error information beyond telling me which task failed within the SSIS package.

I simply want to get to a point where I can at last see SOMETHING about the error(s).

If the answer is that I first need to configure an OnError event in my package, then my next question is: what would the basic, "go to" designer-flow look like for that OnError event?

FYI, this question is similar to "best way of logging in SSIS"

I also noticed an overall strategy for success with SSIS in this answer. The author says:

Instrument your code - have it make log entries, possibly recording diagnostics such as check totals or counts. Without this, troubleshooting is next to impossible. Also, assertion checking is a good way to think of error handling for this (does row count in a equal row count in b, is A:B relationship really 1:1).

Sounds good! But I'd like to have a more concrete example...particularly for feeding me the basics of what specific errors were generated.

I'm trying to avoid learning ALL the SSIS diagnostic approaches, just for the purpose of picking one good "all around" approach.

Update

Per Nick.McDermaid suggestion, in the SSISDB DB I run this:

SELECT * FROM [SSISDB].[catalog].[executions] ORDER BY start_time DESC

This shows to me the packages that I manually executed. The timestamps correctly reflect when I ran them. If anything is unusual(?) it is that the reference_id, reference_type and environment_name columns are null. All the other columns have values.

Update #2

I discovered half the answer I'm looking for. The reason no error information is available, is because by default the SSIS package execution logging level is "none". I had to change the logging level.

Nick.McDermaid gave me the rest of the answering by explaining that I don't need to dive into OnError tooling or SSIS logging provider tooling.

Answer 1

I'm not sure what the issue with your reports are but in answer to the question "Which SSIS diagnostics should I learn", I suggest the vanilla ones out of the box.

In other words use built in SSIS logging (which does not require any additional code) to log failures. Then use the built in reports (once you get them working) to check those logs.

vanilla functionality requires no maintenance. Custom functionality (i.e. filling your packages up with OnError events) requires a lot more maintenance.

You may find situations where you need to learn some of the SSISDB tricks to troubleshoot but in the first instance, try to get everything you can out of the vanilla reports.

If you need to maintain an SQL 2012 or after existing system, then all of this logging is built in. Manual OnError additions are not guaranteed to be built in

The only other thing to be aware of is that script tasks never yield informative errors. I actually suggest you avoid the use of script tasks in SSIS. I feel that if you have to use a script task, you might be using the wrong tool

Answer 2

Adding to the excellent answer of @Nick.McDermaid.
I use SSIS Catalog error reporting. In most cases, it is sufficient and have the following functionality for error analysis. Emphasis is on the following:

• Usually the first or second error message contains meaningful information on error. The latter is some error occurred in the dataflow....
• If you look at the first/second error message at All Messages report at Error Messages section, you will see Error Context hyperlink. Invoking it will show you environment, connection managers and some variables at the moment of package crash.

Good error analysis is more an approach and practice than a mere tool selection. Here are my recommendations:

• SSIS likes to report error code instead of meaningful explanation. So, Integration Services Error and Message Reference is your friend.
• SSIS includes in error context (see above) dump those variables which have Include in ErrorDump property set to true.
• I build SQL commands for SQL Task or DataFlow Source in variables. This allows to display SQL command executed at error in error context, when you set incude in Dump property on these variables.
• Structure your variables well. If some variable is used only at some task - declare it on this task. Otherwise a mess of dumped variables will hurt you more than do any good.