{"id":233,"date":"2011-12-23T14:12:32","date_gmt":"2011-12-23T21:12:32","guid":{"rendered":"http:\/\/www.sqldbpros.com\/?p=233"},"modified":"2011-12-23T14:15:26","modified_gmt":"2011-12-23T21:15:26","slug":"ssis-no-column-information-was-returned-by-the-sql-command","status":"publish","type":"post","link":"http:\/\/sqldbpros.com\/wordpress\/2011\/12\/ssis-no-column-information-was-returned-by-the-sql-command\/","title":{"rendered":"SSIS: No column information was returned by the SQL command"},"content":{"rendered":"

Are you trying to use a stored procedure as the source of a SQL Server Integration Services (SSIS) data flow task?\u00a0 If your stored procedure contains a temp table you may run into the message \"No column information was returned by the SQL command\" (complete error message below).\u00a0 Although there are a number of solutions to this problem many of them have real performance and maintainability implications.\u00a0 Luckily, there's a very simple solution which should work well for most situations.<\/p>\n

\"\"<\/a>When developing an ETL package using SQL Server Integration Services (SSIS) my preference is to create a stored procedure on the source database that will return the required results (an idea supported by the brilliant minds at SQL Best Practices<\/a>).\u00a0 This helps ensure your ETL's needs are taken in to account when an impact analysis or refactoring is performed in the source system.\u00a0 It also adds all the performance and maintainability benefits of stored procedures.<\/p>\n

Unfortunately, if the stored procedure is using a temp table (#table) you'll likely encounter the warning \"No column information was returned by the SQL command\".\u00a0 Essentially, because of the temporary table SSIS can't determine what columns will be returned by the proc and thus won't let you map them to a destination.<\/p>\n

If you do a bit of searching you'll find a common solution to this problem is to SET FMTONLY OFF prior to the proc execution.\u00a0 For example:<\/p>\n

SET FMTONLY OFF
\nEXEC dbo.GetSourceData<\/p>\n

\"\"<\/a><\/p>\n

Although this solution will work it has a potential performance limitation in that it will force the proc to actually execute so it can collect the column meta data.\u00a0 This can cause a number of obvious problems particularly if the stored procedure modifies any data but also from a performance perspective.<\/p>\n

One of the other common solutions is to create a \"fake\" resultset at the beginning of your procedure (such as in this example\u00a0Using Temp Tables in Data Flow Source Stored Procedure<\/a>).\u00a0 This allows SSIS to pickup the column names and datatypes.\u00a0 The fake result set is generally prevented from executing by wrapping it in a conditional logic block which will never be true, such as IF 1=2 or IF 0=1.\u00a0 For example:<\/p>\n

IF 0 = 1
\nBEGIN
\nSELECT\u00a0 CONVERT(VARCHAR(10), NULL) AS AccountCode
\n, CONVERT(VARCHAR(200), NULL) AS CityName
\n, CONVERT(DATETIME, NULL) AS UpdateDate
\nEND<\/p>\n

This solution works well but can become a maintenance headache and slow development if your procedure contains many columns.<\/p>\n

The simplest solution which seems to be overlooked in many cases?\u00a0 Simply convert the temp table to a table variable.\u00a0 With the table variable SSIS is able to gather the required metadata and allowing you to map the source to the destination. Additionally, this requires only a minor change to your T-SQL code and as long as you weren't using an extensive set of indexes on your temp table performance should be very comparable.<\/p>\n

For example replace a structure like this:<\/p>\n

CREATE TABLE #SampleTemp
\n(
\nAccountCode VARCHAR(10) PRIMARY KEY
\n, CityName VARCHAR(200)
\n, UpdateDate DATETIME
\n)<\/p>\n

with the following:<\/p>\n

DECLARE @SampleTemp TABLE
\n(
\nAccountCode VARCHAR(10) PRIMARY KEY
\n, CityName VARCHAR(200)
\n, UpdateDate DATETIME
\n)<\/p>\n

 <\/p>\n

Once that is complete perform a find and replace (CTRL+H)\u00a0 swapping \"#SampleTemp\" with \"@SampleTemp\".<\/p>\n

Now go forth and be awesome!<\/p>\n

\"\"<\/a>
Don't let your column information be invisible to SSIS!<\/figcaption><\/figure>\n

 <\/p>\n

Complete Error Message:<\/p>\n

TITLE: Microsoft Visual Studio
\n------------------------------<\/p>\n

The component reported the following warnings:<\/p>\n

Error at DFT DataFlowTaskName [OLE_SRC OleSourceName [1]]: No column information was returned by the SQL command.<\/p><\/blockquote>\n","protected":false},"excerpt":{"rendered":"

Are you trying to use a stored procedure as the source of a SQL Server Integration Services (SSIS) data flow task?\u00a0 If your stored procedure contains a temp table you may run into the message \"No column information was returned by the SQL command\" (complete error message below).\u00a0 Although there are a number of solutions […]<\/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,46],"tags":[20,10,9,12,11,49,48,5],"class_list":["post-233","post","type-post","status-publish","format-standard","hentry","category-businss-intelligence","category-etl","category-sql-server-integration-services-ssis","tag-business-intelligence","tag-ms-sql","tag-sql-server","tag-sql-server-2005","tag-sql-server-2008","tag-sql-server-integration-services","tag-ssis","tag-tips"],"_links":{"self":[{"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/posts\/233","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=233"}],"version-history":[{"count":5,"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/posts\/233\/revisions"}],"predecessor-version":[{"id":240,"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/posts\/233\/revisions\/240"}],"wp:attachment":[{"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/media?parent=233"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/categories?post=233"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/tags?post=233"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}