Can I find the name of the table in SSIS that threw an error on insert?
There is a rich set of tables and views available in the SSISDB that operate as a flight recorder for SSIS packages as they execute. Markus Ehrenmüller (t) had a great question in Slack. In short, can you figure out what table is being used as a destination and I took a few minutes to slice through the tables to see if I could find it.
If it's going to be anywhere, it looks like you can find it in catalog.event_message_context
If someone is using an OLE DB Destination and uses "Table or view" or "Table or View - fast load" settings, the name of the table will be the event message_context table. If they are using a variable name, then it's going to be trickier.
SELECT EMC.* FROM catalog.event_message_context AS EMC INNER JOIN catalog.event_message_context AS AM ON AM.event_message_id = EMC.event_message_id AND AM.context_source_name = EMC.context_source_name AND AM.context_depth = EMC.context_depth AND AM.package_path = EMC.package_path WHERE -- Assuming OLE DB Destination AM.property_name = 'AccessMode' -- Father forgive me for this join criteria AND EMC.property_name = CASE AM.property_value -- Need to validate these values, look approximate WHEN 0 THEN 'OpenRowset' WHEN 3 THEN 'OpenRowset' --WHEN 4 THEN '' ELSE 'OpenRowsetVariable' END AND EMC.event_message_id IN ( SELECT DISTINCT EM.event_message_id FROM catalog.event_messages AS EM WHERE -- if you know the specific operation/execution id, use it here --EM.operation_id = @id 1=1 AND EM.message_type = 120 );
Let's break that down. We filter against the catalog.event_messages table for message_type of 120 because 120 is "Error". If you know the explicit operation_id that you are interested, remove the 1=1 and patch in that filter.
We'll use that dataset to restrict the EMC aliased table to just things that were associated to the Error. The EMC set pulls back the row that will contain the table name. We need to further figure out which of the many property_values to display. This is where it gets ugly. I think what I have is working but I have a small SSISDB to reference at the moment. We need to pick either the property_name of OpenRowset or OpenRowsetVariable. That's why we join back to the event_message_context table and use the value of the AccessMode to determine what we need to filter the EMC against.
Play with it, see if this helps you. I've added it to my SSIS SSMS Template queries so feel free to mash up those data sets as you see fit. If you find a better way to do it, I'd love to hear about it.