For an ETL process, we have user tables created that are used in place of temporary tables. CDC is enabled on the database on several tables. None of the work tables are marked for capture by CDC. The SSIS package that's failing has a data flow task with an OLEDB Source that executes a stored procedure. The stored procedure drops and recreates a work table, which causes the ddl database trigger to fire and call sp_cdc_ddl_event_internal. This system proc uses a temp table, which is causing the SSIS package to error with the following message:
An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft SQL Server Native Client 11.0" Hresult: 0x80004005 Description: "The metadata could not be determined because statement 'insert #tran_altertable ( DDLsubtype, TableOwner, TableName, ColumnName, ColumnAttr )
exec sys.' in procedure 'sp_cdc_ddl_event_internal' uses a temp table.".
This seems to be an issue with 2012 as we have the same process running on a 2008 R2 instance without the error.
I attempted to work around the problem by altering the trigger to check if the table was in cdc.change_tables before calling the ddl proc, but this doesn't appear to work for SSIS.