March 20, 2026 at 12:15 am
Hi everyone
I am looking at building a query to determine how much time is being spent on each task in SSIS. My SSIS package has a few containers and within each container there are tasks. Once one container finishes processing then the next container gets processed. I have never done this before so I am not sure how to begin. I have been playing around with dbo.sysssislog. I am noticing that some tasks that are completed in SSIS are not appearing when I query it. Here is my query:
select *
from dbo.sysssislog
where convert(date,starttime) = '2026-03-19' and [source] like '%download%'
order by id
The purpose of this query is to make sure that I am capturing all the tasks that have "download" in it. But this is not happening. In my SSIS there are 3 separate tasks that contain the keyword "download" but only one gets returned. I am doing something wrong but I am not sure what. Once I see all the tasks with "download" then I can remove the filter. The end goal is to capture all tasks not just ones that have "download" in them. Can someone please help guide me and let me know what I am doing wrong?
Thank you
March 20, 2026 at 7:33 am
Assuming your SSIS packages are deployed to SSISDB, try looking inside
SSISDB.catalog.operations
March 20, 2026 at 6:41 pm
I haven't deployed anything yet. The SSIS package is still in development mode. What do you suggest?
March 20, 2026 at 10:28 pm
If the package isn't deployed, then Visual Studio tracks the time. No need to look at the database side. If you need to track the time on the database side, deploy it to the TEST database then query the tables.
At least that's my advice on it. Main reason for my approach here is that if I am not mistaken, if it hasn't been deployed and is NOT on the server but running in visual studio, it is using local executable for it and local resources so any timing analysis is going to be mostly useless as it's your local computer's memory and CPU not the servers. So unless your development box has similar resources to the server hosting your SSIS service, you are going to likely have different timing analysis between development and deployment.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
March 21, 2026 at 3:53 am
I have been playing around with this today. I am making some progress but I am still stuck. Here is my query so far:
DROP TABLE IF EXISTS #PRE_DATA
DROP TABLE IF EXISTS #POST_DATA
SELECT *
INTO #PRE_DATA
FROM DBO.sysssislog
WHERE [EVENT] = 'OnPreExecute'
SELECT *
INTO #POST_DATA
FROM DBO.sysssislog
WHERE [EVENT] = 'OnPostExecute'
These two events capture the start and end times so now the big challenge is how do I join the tables. SourceID uniquely defines each task so I can join #PRE_DATA.SourceID = #POST_DATA.SourceID so this will match up the start and end time (ie, starttime from #PRE_DATA is the start time of the task and starttime from #POST_DATA is the end time of the task). The SourceID is static across time so the join is not going to produce quite what I had in mind. There are many matches being matched to each other so its giving way too many results. I need to modify the query so it recognizes the different SSIS runs. Sometimes the SSIS package is run 3 times a day so in that case I would want 3 sets of results (one for each time the SSIS package is run). How can I do this?
March 21, 2026 at 5:58 am
I figured out a way. I add a join on EXECUTIONID and then the records get mapped correctly. Problem solved!
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply