Using Stored Procedure (in SSIS) returning data through Temporary Table / Table Variable

Objective : There is an Stored Procedure which is returning records from a temporary table / table variable.
The SP needs to be executed through an 'OLE DB source' -> Data Flow Task -> SSIS


Using Temporary Table:

Store Procedure Name : "SP_Using_TempTable"

/* Store Procedure script */

CREATE PROCEDURE [dbo].[SP_Using_TempTable]
AS
BEGIN

SET NOCOUNT ON

SELECT * INTO #temp1 FROM dbo.ErrorDetails

SELECT * FROM #temp1

END

Figure 1

Figure 2

The important points/properties are :

• The columns of temporary table will appear for mapping.
• SET NOCOUNT ON
• SET FMTONLY OFF

Using Table Variable:

Store Procedure Name : " SP_Using_TableVariable "

/* Store Procedure script */

CREATE PROCEDURE [dbo].[SP_Using_TableVariable]
AS
BEGIN

SET NOCOUNT ON

DECLARE @temp_table TABLE
(
Error_Code varchar(50),
Error_Description varchar(500)
)

INSERT INTO @temp_table
SELECT Error_Code, Error_Description 
FROM dbo.ErrorDetails

SELECT * FROM @temp_table

END

Figure 3

The important points/properties are :

• The columns of temporary table will appear for mapping.
• SET NOCOUNT ON
• NO Need to SET FMTONLY OFF