As soon as I saw this error message I guessed the problem was related to using a temp table. What was surprising was that the error message actually said this but I didn’t realize it until by chance I cut and paste the message into another window. Look below: Do you see the reference to the temp table?
Exception from HRESULT: 0xC020204A Error at Data Flow Task Export to FlatFile [OLE DB Source ]: 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 'SELECT [id] ,[SerialNumber] ,Model ,Build ,Version A' uses a temp table.". Error at Data Flow Task Export to FlatFile [OLE DB Source ]: Unable to retrieve column information from the data source. Make sure your target table in the database is available.
Alright, now that I’ve moved it to another window it may be easier to see but in Visual Studio in the tiny error window it was really hard to see! (in case you missed it in the example above look at the line which begins with “,Version”).
Now that you’ve got it how do you fix it?
Pretty easy, taking a page from our article SSIS, Stored Procedures, and Temp Tables I tried declaring the table variable at the beginning of the query instead of performing a SELECT…INTO. (I’m pretty sure creating the temp table first would have also worked).
Boom. Problem solved!