What table raised the error in SSIS?

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.