SSIS no column information was returned by the sql command

Question

1. I ran my Stored Procedure in BI_Test_Server.

2. Then I executed it using exec [dbo].[bi_resource_dimension_sp] 1,1

3. I got the results in SQL Server management Studio successfully. So I created a Data Flow task, put the OLE DB source, Data Connection and OLE DB destination. I edited OLE DB source and created a new connection. Inside OLE DB source SQL Command : SQL Command Text I put the Step 4.

4. In the OLE DB Source – SQL Command Text, I put the exec [dbo].[bi_resource_dimension_sp] 1,1 in order to select all the data from the OLE DB source then connect it to the Data Connection and dump the data in the OLE DB Destination.

5. But when I try to preview the results, I get the error that no column information was returned by the SQL command.

Ideally, I need to tick all of the check boxes to make sure in which column to get and put the data. Which is what I'm trying and about to do after Step 4.

It says on the error that you may “Choose Ok if you want to continue with the operation”. But if I do that, when I go the columns section (on the top left portion between connection manager and error output). It’ll show me blank columns.

Please help.

Answer 1

A workaround that worked for me, thanks to my colleague, Iraldo Gomez, for suggesting it:

IF (1 = 2)
  Select <column_list> From <table>
Exec <stored_procedure_name>

All I had to do was to create a select on the top that would never run, but returns the same columns as the stored procedure. SSIS reads the column_list from the select statement and we achieve what we're looking for.

Answer 2

I ran into this problem using a SQL Query (not a stored procedure) and eventually discovered that having a comment at the start of my query for my own eventual re-edification was the source of my grief.

--this is a query
select FieldA, 
FieldB 
from SomeTable

fails with the "no column information" error, while

Select 
--this is a query
FieldA, 
FieldB 
from SomeTable

works.

This was with SSIS 2012.

Answer 3

I also got this error because the SP database and the Connection database are different. Doing both the same fixed it.

Answer 4

This issue is comes into picture when Importing only data from excel using SQL Command, and headers not deriving from excel then when we put the connection string dynamic then this issue come into picture::

For a example I want to read data Range A2:P by SQL Command. SELECT F1,F2,F3,F4,F5,F6,F7,F8,F9,F10,F11,F12,F13,F14,F15,F16 FROM [Data$A2:P]

Till now you will not see any error but when you change the connection String to make it dynamic then a error is start showing at your Excel Source.

Now the Solution is very easy just check the the excel file which it's picking after dynamic connection ,open that file and select All cell and save it now make the connection again after deleting first or refresh first one , error will automatically gone!!! I hope it will help you !!!

Answer 5

I had this error pop up while using the OLE DB source connection to an Oracle server in an SSIS package data flow. I set the SQL statement up in a SQL Command (text) and I had placed a comment as the first line in the command. Removing the comment solved the problem.

-- Current sql select for Extended Claims: <<<< Remove comment, it will work SELECT CH.CLAIM_NUMBER, CD.LINE_NUMBER, CD.SUB_LINE_CODE, CD.SEQ_PROV_ID, ...

Answer 6

I got this error with an Excel Source data flow task in SSIS:

I resolved the error by deleting the Excel Source task and then creating it again.

Answer 7

You could try using "OLE DB Command" in Data Flow. This made a difference for me. I am using VS 2013 and using the same procedure with "OLE DB Destination" is not working, but with "OLE DB Command" is fine. I noticed some obstacles with "OLE DB Command" also, especially with Stored Procedures what are not correct, but that is a plus for SSIS.

Data flow tasks are very strict with retrieving columns/parameters on which it can do the SQL, especially for OLE DB Destination(now I notice it is for Source also). most often with this error. It is recommended by Microsoft(OLE DB Destination recommendations) to use OLE DB command instead of destination. I would also recommend using stage temporary tables when possible. Even with the OLE DB Command, I found a need to refresh it a couple of times so the input/destination columns/procedure parameters came in available for mapping.

Here is the note(not sure if it is applicable for source, but could be):

Note

The OLE DB destination does not support parameters. If you need to execute a parametrized INSERT statement, consider the OLE DB Command transformation. For more information, see OLE DB Command Transformation.

Answer 8

I am thinking you may have a column in your stored procedure that needs an alias. Do you have something like a COUNT() or SUM() in your SQL that does not have and alias? When you run the stored procedure in SSMS, do you see a "(No Column Name)" returned anywhere?

Answer 9

Not a solution but just a workaround!

Dump the data from your SP into an intermediate temporary table, which you can create in an Execute SQL Task.

CREATE TABLE #Temp
(
.....
.....
)
insert into #Temp
exec [dbo].[bi_resource_dimension_sp] 1,1

Set the Delay Validation property to True and the RetainSameConnection property to the OLEDB connection to True. Now, in the DFT, instead of the SP, source data from the #temp table. As all the columns have alias in a table, you should not be facing this issue anymore.

Answer 10

SQL DB Pros has the answer. The SET FMTONLY OFF solution worked for me - performance wasn't really an issue since my SP had dynamic SQL anyway.