No column info from stored procedure

Submitted by:Jhon Brain

Date added:08 May, 2013

Category:SQL

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.

Tags: stored procedure

Code 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
 
 

Comments