CODEWITHSUNDEEP
sql-serverssissql-server-2017
Alex Sham
Alex Sham

Reputation: 468

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

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:

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

  1. Execute Sql Task (name = "Start")
  2. Data Flow Task (name = "Load")
  3. Script Task (name = "Check")
  4. 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?

Upvotes: 2

Views: 1054

Answers (2)

Ferdipux
Ferdipux

Reputation: 5256

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.

Upvotes: 2

BI Dude
BI Dude

Reputation: 2016

I think you are after the view below:

catalog.execution_component_phases

use SSISDB  
select package_name, task_name, subcomponent_name, execution_path,  
    SUM(DATEDIFF(ms,start_time,end_time)) as active_time,  
    DATEDIFF(ms,min(start_time), max(end_time)) as total_time  
from catalog.execution_component_phases  
where execution_id = 1841  
group by package_name, task_name, subcomponent_name, execution_path  
order by package_name, task_name, subcomponent_name, execution_path

If view is empty then set the logging level of the package execution is set to Performance or Verbose.

More information:

https://learn.microsoft.com/en-us/sql/integration-services/system-views/catalog-execution-component-phases?view=sql-server-ver15

Upvotes: 1

Related Questions