CODEWITHSUNDEEP
sql-serverssis
Jamie Alford
Jamie Alford

Reputation: 95

Can / how do I identify which step in an SSIS package failed from SSIS logs?

If I have a SQL Agent job that runs an SSIS package and that SSIS package fails. Is it possible / how do I identify which step in the SSIS package failed?

My end goal is to write a query that will tell me the last step that failed. So far I have:

select top 5 * from MyDB.dbo.sysssislog 
where event = 'OnError'
order by id desc

This gives me a SourceID, which is the GUID of the package (which I already know from SQL Agent view history). It also gives me executionid, which I haven't been able to match up to anything.

I found the Microsoft docs useful https://learn.microsoft.com/en-us/sql/integration-services/system-views/views-integration-services-catalog, however, I wasn't able to identify which step in the package caused the problem. I am wondering if it is possible at all - if SSIS simple doesn't store this information.

Upvotes: 1

Views: 1397

Answers (1)

Jens
Jens

Reputation: 3299

Alternatively, if you just want to check it yourself you can see logging from your executed SSIS package in your SQL Server Management Studio:

SSMS

Upvotes: 1

Related Questions