locked
No Column information was returned by the SQL command. RRS feed

  • Question

  • I can preview the SQL command in the OLE DB Source Editor and bring back all columns and results just fine but when I click on the Columns I get

    TITLE: Microsoft Visual Studio
    ------------------------------

    The component reported the following warnings:

    Error at Data Flow Task [OLE DB Source [1]]: No column information was returned by the SQL command.

    The columns are there in the preview - why can't SSIS get the column information?

    TIA

    Harry


    Friday, November 13, 2015 5:49 PM

Answers

  • I actually changed all the temp tables to table variables and replaced the little bit of dynamic SQL with direct commands to another server that is already linked to the server the query runs on and started the SSIS from scratch and all of that worked.  Thank you for your assistance.

    • Marked as answer by Mimosa Arts Tuesday, November 17, 2015 9:39 PM
    Tuesday, November 17, 2015 9:39 PM

All replies

  • Delete it, and recreate it... sounds like the meta data has gone wonky.

    Don't forget to mark helpful posts, and answers. It helps others to find relevant posts to the same question. Enjoyed my post? Hit the up arrow (left)
    Really enjoyed it? See my profile!
    My Tech Net Articles. Help me change the face of men’s health this Movember by making a donation to my moustache: http://mobro.co/reeb

    Friday, November 13, 2015 5:53 PM
  • Hi Harry,

    Based on the error message, we can infer that the issue is the SQL command doesn’t return result.

    In SQL Server Integration Services, the OLE DB Source extracts data from a variety of OLE DB-compliant relational databases by using a database table, a view, or an SQL command. But your SQL command doesn’t return anything, we cannot type it in OLE DB Source. So the error occurs.

    Please make sure you type the correct SQL command that return result in OLE DB Source, you can execute the same query in SSMS to test the issue. If issue still exists, we can recreate the OLE DB Source to check again. If possible, please post the sample query with sample data for use reference.

    Thanks,
    Katherine Xiong


    Katherine Xiong
    TechNet Community Support

    Tuesday, November 17, 2015 2:53 AM
  • Can I ask what is the source you're connecting to? Also is it a procedure or a sql query that you're using? Are you sure you aliased all expression based columns in the resultset?

    If possible can you post the query used 


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Tuesday, November 17, 2015 3:30 AM
  • Hi Harry,

    Try adding below command before your code block - http://www.sqldbpros.com/2011/12/ssis-no-column-information-was-returned-by-the-sql-command/

    SET FMTONLY OFF

    https://msdn.microsoft.com/en-us/library/ms173839.aspx


    sathya - www.allaboutmssql.com ** Mark as answered if my post solved your problem and Vote as helpful if my post was useful **.


    • Edited by SathyanarrayananS Tuesday, November 17, 2015 3:53 AM reference link
    • Proposed as answer by Roul174 Wednesday, February 12, 2020 3:15 PM
    Tuesday, November 17, 2015 3:49 AM
  • I actually changed all the temp tables to table variables and replaced the little bit of dynamic SQL with direct commands to another server that is already linked to the server the query runs on and started the SSIS from scratch and all of that worked.  Thank you for your assistance.

    • Marked as answer by Mimosa Arts Tuesday, November 17, 2015 9:39 PM
    Tuesday, November 17, 2015 9:39 PM
  • Hi Harry,

    Try adding below command before your code block - http://www.sqldbpros.com/2011/12/ssis-no-column-information-was-returned-by-the-sql-command/

    SET FMTONLY OFF

    https://msdn.microsoft.com/en-us/library/ms173839.aspx


    sathya - www.allaboutmssql.com ** Mark as answered if my post solved your problem and Vote as helpful if my post was useful **.


    thank you for this tip. my problem was to setup datasource from Sybase, i used standard OLE DB source, next used EXEC ... AT [LinkedSybaseServer] wirh result sets ((colum1 [datatypeN],...)) but no possible to read metadata (**). With FMTONLY switch DataFlow task is enable to read metadata and world has been saved :-)

    Thank you so much!

    Ondrej, Czechia

    (**)

    TITLE: Microsoft Visual Studio
    ------------------------------

    The component reported the following warnings:

    Error at Data Flow Task [Sybase map procedure source [25]]: No column information was returned by the SQL command.


    Choose OK if you want to continue with the operation.
    Choose Cancel if you want to stop the operation.

    ------------------------------
    BUTTONS:

    OK
    Cancel
    ------------------------------
    Wednesday, February 12, 2020 3:22 PM