{"id":772,"date":"2014-11-13T07:00:33","date_gmt":"2014-11-13T14:00:33","guid":{"rendered":"http:\/\/www.sqldbpros.com\/?p=772"},"modified":"2014-11-06T17:24:22","modified_gmt":"2014-11-07T00:24:22","slug":"troubleshooting-ssis-unable-to-retrieve-column-information-from-the-data-source-make-sure-your-target-table-in-the-database-is-available","status":"publish","type":"post","link":"http:\/\/sqldbpros.com\/wordpress\/2014\/11\/troubleshooting-ssis-unable-to-retrieve-column-information-from-the-data-source-make-sure-your-target-table-in-the-database-is-available\/","title":{"rendered":"Troubleshooting: SSIS Unable to retrieve column information from the data source. Make sure your target table in the database is available."},"content":{"rendered":"<p>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. \u00a0Look below: Do you see the reference to the temp table?<\/p>\n<pre>Exception from HRESULT: 0xC020204A\r\nError at Data Flow Task Export to FlatFile [OLE DB Source [191]]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.\r\nAn 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\r\n [id]\r\n ,[SerialNumber]\r\n ,Model\r\n ,Build\r\n ,Version A' uses a temp table.\".\r\nError at Data Flow Task Export to FlatFile [OLE DB Source [191]]: Unable to retrieve column information from the data source. Make sure your target table in the database is available.<\/pre>\n<p>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\").<\/p>\n<p>Now that you've got it how do you fix it?<\/p>\n<p>Pretty easy, taking a page from our article<a title=\"SSIS: No column information was returned by the SQL command\" href=\"http:\/\/www.sqldbpros.com\/2011\/12\/ssis-no-column-information-was-returned-by-the-sql-command\/\"> SSIS, Stored Procedures, and Temp Tables<\/a> I tried declaring the\u00a0table 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).<\/p>\n<p>Boom. Problem solved!<\/p>\n","protected":false},"excerpt":{"rendered":"<p>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. \u00a0Look below: Do you see the reference to [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[22,47,43,46,57,50],"tags":[20,117,49,143],"class_list":["post-772","post","type-post","status-publish","format-standard","hentry","category-businss-intelligence","category-etl","category-microsoft-sql-server","category-sql-server-integration-services-ssis","category-t-sql","category-visual-studio","tag-business-intelligence","tag-sql-server-2012","tag-sql-server-integration-services","tag-vs-2012"],"_links":{"self":[{"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/posts\/772","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/comments?post=772"}],"version-history":[{"count":4,"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/posts\/772\/revisions"}],"predecessor-version":[{"id":776,"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/posts\/772\/revisions\/776"}],"wp:attachment":[{"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/media?parent=772"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/categories?post=772"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/tags?post=772"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}