Sooner or later you'll create a DataFlow and come across this type of dialog box for the OLE DB source:
No source columns listed and noting to map - great 
So, why does this problem occur, when all previous attempts have worked? Well the most likely case is that you are selecting from a temporary table as the last statement in the source stored procedure, and in this case there is no metadata returned to the designer. Just a simple proc like the one below will cause this problem:
CREATE PROC myproc AS
SELECT TOP 10 organisation_name INTO #MyOrganisations FROM dbo.Organisation
SELECT * FROM #MyOrganisations
So, what can we do? There are 2 main possibilities (there is also a third one that I'll post up some other time).
(1) One possibility is to change the SQL Command Text as follows and add the italicised text:
SET FMTONLY OFF
SET NOCOUNT ON
exec myproc
This forces the parser to actually run the query to return the metadata and reverse the SET FMTONLY ON that it implicitly uses.
This works, and you'll see the column names as per normal. However there is a serious downside that seems to be missed when this solution is advised. My colleague John Gillies added an auditing step to the proc "myproc" to test this and found that the proc itself was executed 5 times when running the package!!!This could seriously slow things down if the initial stored proc itself is slow anyway. If the proc also does some data modifications, it could be catastrophic.
However, the problems can be offset somewhat and the performance can be optimised by using 2 properties:
I set “DelayValidation” on the Data Flow task to true and the executions of the stored proc went down from 5 to 3 (as the result of a recommendation by Phil Brammer).
Then I set the “ValidateExternalMetadata” setting of the source and dest connections to be false. This further reduced the executions to 2.
Better than 5 but still not down to the 1 execution that I was hoping to achieve.
(2) Use a Table Variable instead
CREATE PROC myproc AS
declare @mytable table (organisation_name varchar(100))
INSERT INTO @mytable(organisation_name)
SELECT TOP 10 organisation_name FROM dbo.Organisation
SELECT * FROM @mytable
You'll now need to set the SQL Command Text to be:
SET NOCOUNT ON
exec myproc
Otherwise you'll get the dreaded "A rowset based on the SQL command was not returned by the OLE DB provider" error.
Using this technique on our procedures ensured that the procruns once only! OK - you can't put nonclustered indexes on a table variable and all the other restrictions, so if really needed you can use option (1) but in most cases I'd reason that option (2) is the preferable solution.
