Scenario:  Using a stored procedure as the source in an OLE_DB Source for SSIS,  the package can’t detect the output of the stored proc.

Error:  “No column information was returned by the SQL command”

Solution:  Add a no-op statement at the beginning of the stored procedure to set up the pipeline.

Snippet:

If Object_Id('[dbo].[GenMetadata]', 'P') Is Not Null
Drop Procedure [dbo].[GenMetadata]
Go
Create Procedure [dbo].[genMetAdAta]
As
Set NoCount On
/*********** This is what to add ***********************/   If 1 = 0
     Begin
         -- Publish metadata
         Select
           Cast(Null As Int)           As Id
           , Cast(Null As Nchar( 10 )) As [Name]
           , Cast(Null As Nchar( 10 )) As SirName
     End

/*********** end add ***********************/
 -- Do real work starting here
Create Table #Test
 (
 [id]        [Int] Null ,  [Name]    [nChar]( 10 ) Null ,
[SirName] [nChar]( 10 ) Null )

 Insert Into #Test
 Select '1', 'A', 'Z'
 Union All
 Select '2', 'b', 'y'
 Select
 Id, [Name], SirName
 From   #Test
 Drop Table #Test

Return
Go 

Source: jaegd – SQL Server Forums