No column info from stored procedure

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.


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

/*********** 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'
 Id, [Name], SirName
 From   #Test
 Drop Table #Test


Source: jaegd – SQL Server Forums

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: