How to detect a SSIS package component type in SSISDB?

I have a SQL, which helps to analyse every SSIS package component duration, it uses

SSISDB.catalog.executions
SSISDB.CATALOG.operations
SSISDB.CATALOG.event_messages

The fact is, I can't understand from data in this entities, what does event_messages.message_source_name stand for. I have only its name and its path in package. I need to understand every component type:

SQL Script Task
Data Flow
Sequence container
Loop container
and so on

For example, I have a SSIS package (name = "Test") with 4 components:

Execute Sql Task (name = "Start")
Data Flow Task (name = "Load")
Script Task (name = "Check")
Execute Sql Task (name = "Finish")

And querying SSISDB I want to get something like this

Package_name |Component_name | Component_type
Test         | Start         | Execute Sql Task
Test         | Load          | Data Flow Task 
Test         | Check         | Script Task
Test         | Finish        | Execute Sql Task

Is it possible?

Solution

The fact is, I can't understand from data in this entities, what does event_messages.message_source_name stand for.

It describes object name, defined in Name property, which generated this message record. Together with message_source_id it allows to identify source of the message. I.e. in your case it can be the name of the package or name of the task or container or dataflow.
Based on the event log information, you can only distinguish between the following objects:

Package
Task
Container
Data Flow Task

Good news - this table is filled on Standard logging level.

There is no easy way to get component type from SSISDB. To get component type you need to analyze package source code. The problem is that it is stored encrypted in internal.packages. You can export the whole project which contains the package you are interested in with catalog.get_project SSISDB stored procedure, and then unzip it and read XML source of the referred package. By using message_source_id GUID you can identify task in XML and read its type.