SSIS: No column information was returned by the SQL command


Are you trying to use a stored procedure as the source of a SQL Server Integration Services (SSIS) data flow task?  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).  Although there are a number of solutions to this problem many of them have real performance and maintainability implications.  Luckily, there’s a very simple solution which should work well for most situations.

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).  This helps ensure your ETL’s needs are taken in to account when an impact analysis or refactoring is performed in the source system.  It also adds all the performance and maintainability benefits of stored procedures.

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”.  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.

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.  For example:

SET FMTONLY OFF
EXEC dbo.GetSourceData

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.  This can cause a number of obvious problems particularly if the stored procedure modifies any data but also from a performance perspective.

One of the other common solutions is to create a “fake” resultset at the beginning of your procedure (such as in this example Using Temp Tables in Data Flow Source Stored Procedure).  This allows SSIS to pickup the column names and datatypes.  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.  For example:

IF 0 = 1
BEGIN
SELECT  CONVERT(VARCHAR(10), NULL) AS AccountCode
, CONVERT(VARCHAR(200), NULL) AS CityName
, CONVERT(DATETIME, NULL) AS UpdateDate
END

This solution works well but can become a maintenance headache and slow development if your procedure contains many columns.

The simplest solution which seems to be overlooked in many cases?  Simply convert the temp table to a table variable.  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.

For example replace a structure like this:

CREATE TABLE #SampleTemp
(
AccountCode VARCHAR(10) PRIMARY KEY
, CityName VARCHAR(200)
, UpdateDate DATETIME
)

with the following:

DECLARE @SampleTemp TABLE
(
AccountCode VARCHAR(10) PRIMARY KEY
, CityName VARCHAR(200)
, UpdateDate DATETIME
)

 

Once that is complete perform a find and replace (CTRL+H)  swapping “#SampleTemp” with “@SampleTemp”.

Now go forth and be awesome!

Don't let your column information be invisible to SSIS!

 

Complete Error Message:

TITLE: Microsoft Visual Studio
——————————

The component reported the following warnings:

Error at DFT DataFlowTaskName [OLE_SRC OleSourceName [1]]: No column information was returned by the SQL command.


Leave a Reply

Your email address will not be published.