SSIS task and transformation types in SSISDB

Question

SSIS task and transformation types in SSISDB

DamianC

SSCrazy Eights

Points: 8015
More actions
October 16, 2019 at 1:49 pm

#3689277

Hello

Does SSISDB hold the type of task anywhere
It would make querying for slow tasks or data transformations much easier
Also, look at history to see if specific transformation timescales have changed etc.

Thanks

- Damian

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Jeffrey Williams SSC Guru Points: 90244 More actions · Answer 1

If you are using the Integration Services Catalog - then you have access to the All Executions report which will show you how long each task takes when the package is executed.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

DamianC SSCrazy Eights Points: 8015 More actions · Answer 2

Thanks Jeffrey

The report does show that information but I can't look at a specific task (or type of task) over time

e.g. evidence of a specific dataflow that's getting slower or which data flow takes the longest (not just as a one off)

Just so I can drill around and look at times etc.

I'm aware there are other methods (Query Store, dmvs or a 3rd party tool like solar winds) but these focus on queries

Unless I am missing an entirely different approach here?

Thanks

- Damian

DinoRS SSCrazy Points: 2683 More actions · Answer 3

You can query

[SSISDB].[internal].[executable_statistics]

and get some runtimes, you will be able to see how long a DFT from beginning to end will take but not for steps within that DFT, things like Script Tasks could be tracked in duration like that.

You could always enable logging via log providers or change the default logging level.

DamianC SSCrazy Eights Points: 8015 More actions · Answer 4

Yes, I looked at the logging level but there doesn't seem to be one for this

I had a look at executable_statistics but this doesn't seperate out DFT unless we implement a naming convention

Naming is another option but I was wondering if we could do without

Thanks

- Damian