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

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.