Query dtexec package executions

Question

We have a SQL Server where several packages are executed using dtexec, and I would like to retrieve the execution information and from which machines they are executed, but I can’t figure out where to find it. In the executions view, there are only the packages deployed in the Integration Service Catalog, not those executed with dtexec. Do you know how to retrieve this information?

Thanks in advance

Simone

Answer 1

@Simone Bottani,

Starting from SSIS 2012 onwards, it is better to deploy SSIS Projects to SSIS Catalog (SSISDB database behind the scenes). That way you will have a full data on SSIS packages execution, and much more.

I personally use the following T-SQL query to retrieve SSIS packages execution info.

USE SSISDB;
GO
/*
https://docs.microsoft.com/en-us/sql/integration-services/system-views/catalog-executions-ssisdb-database?redirectedfrom=MSDN&view=sql-server-ver15
*/
SELECT duration = CONVERT(CHAR(12), DATEADD(MILLISECOND, msec + 86400000, 0), 114)
	, t2.status_name, FORMAT(created_time,'yyyy-MM-dd HH:mm') AS start_time, *
FROM CATALOG.executions as x
INNER JOIN (VALUES (1, 'Created', 'Active')
		, (2, 'Running', 'Active')
		, (3, 'Canceled', 'Failed')
		, (4, 'Failed', 'Failed')
		, (5, 'Pending', 'Active')
		, (6, 'Ended unexpectedly', 'Failed')
		, (7, 'Succeeded', 'Succeeded')
		, (8, 'Stopping', 'Active')
		, (9, 'Completed', 'Failed')) as t2(status_id, status_name, status_group)
ON x.status = t2.status_id
	CROSS APPLY (VALUES (DATEDIFF(millisecond, start_time, end_time))) as t(msec)
WHERE folder_name = '...'
AND project_name = '...'
AND package_name = '....dtsx'
ORDER BY execution_id DESC;

Answer 2

If you haven't implemented an own logging, then you can't get the information who/where/when a SSIS package was executed directly with the dtexec tool.

Answer 3

Hi @Simone Bottani,

Here is a same thread on Q&A, you may take a reference to.

Find which computer ran an SSIS package

Regards，

Zoe Hui

---

If the answer is helpful, please click "Accept Answer" and upvote it.