question

I have a Data Flow operator that is in a ForEach loop that is to collect data from each database on a server. Repeatedly, for some databases, this results in an error. The data source is DECLARE @LogInfo TABLE ( [RecoveryUnitID] INT , [FileId] INT , [Filesize] BIGINT , [StartOffset] BIGINT , [FSeqNo] INT , [STATUS] INT , [Parity] INT , [CreateLSN] NUMERIC(25, 0) ) DECLARE @sql NVARCHAR(100); SET @sql = 'DBCC LOGINFO' INSERT @LogInfo EXEC [sys].[sp_executesql] @sql; SELECT @@servername AS [ServerName] , DB_NAME(DB_ID()) AS [DBName] , COUNT(*) AS [VLFCount] FROM @LogInfo AS LI; The SSIS error handler returns three errors: **1.** > SSIS Error Code DTS_E_OLEDBERROR. 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 'IF EXISTS ( SELECT * FROM (SELECT ObjDev = @Command) ObjDev' in procedure '[procname]' uses a temp table.". **2.** > Unable to retrieve column information from the data source. Make sure your target table in the database is available. **3.** > Sources Databases failed the pre-execute phase and returned error code 0xC020204A. So far this seems to be predominantly occurring in **mdw** and **sysutility_mdw** databases (created by invoking MDW and Utility Control Point functionality in SQL Server) but I would like to understand why before I just start dismissing errors. Its occurring on SQL Server 2008 R2 and 2012 instances. Can anyone suggest why and how to mitigate this please?