Search code examples
sql-serverssisssis-2016

Basic Approach to Diagnostic Logging in SSIS

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:

enter image description here

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.

Solution

  • 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