OLE DB Source

Within SSMS, I execute my stored procedure below, it returns two temp tables from the result set.

DECLARE      @AsOfDate      CHAR(8)
-- Assigning values to the @AsOfDate
SET @AsOfDate = CONVERT(CHAR(8), GETDATE(), 112)
EXECUTE [PROD].[dbo].[cIssues] @AsOfDate

When I try to use OLE DB Source, with SQL command data access mode, and the SQL command text below, and preview it, it returns only the first temp table from the stored procedure.
SET FMTONLY OFF
SET NOCOUNT ON
DECLARE      @AsOfDate      CHAR(8)
-- Assigning values to the @AsOfDate
SET @AsOfDate = CONVERT(CHAR(8), GETDATE(), 112)
EXECUTE [PROD].[dbo].[cIssues] @AsOfDate

Is there other toolbox within SSIS that allows me to execute stored procedure that returns two temp tables and use the full result set?

Thanks
emailamosAsked:
Who is Participating?
 
Reza RadConsultant, TrainerCommented:
I'm not clear about your question.
did you filled results in temp tables?
or you just returned them by select .... at the end of stored procedure?

if you filled results in temp tables, so follow guidlines in this link to work with temp tables:
http://consultingblogs.emc.com/jamiethomson/archive/2006/11/19/SSIS_3A00_-Using-temporary-tables.aspx

if you want to return data by selecting them at the  end of stored procedure, Note that you can only have SINGLE result set, if  you have multiple results SSIS will fetch only first result set.
0
 
Alpesh PatelAssistant ConsultantCommented:
Please use RecordSet in Dataflow to get multiple table.
0
 
emailamosAuthor Commented:
How to execute the stored procedure in RecordSet?
0
 
Alpesh PatelAssistant ConsultantCommented:
Take OLEDB Data Source and set the SQL Command and pass parameter.

sp_test ?, ?

In parameters tab pass parameter
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.