SSIS set returns error on some database connection
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?
This is happening because your connection is trying to validate objects mentioned in Source script, but as they dn't exits it will throw error. Set 'ValidateExternalMetaData' property of your OLEDB source in DFT to 'FALSE' . This might help you.