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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Alpesh PatelAssistant ConsultantCommented:
Please use RecordSet in Dataflow to get multiple table.
0
emailamosAuthor Commented:
How to execute the stored procedure in RecordSet?
0
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Alpesh PatelAssistant ConsultantCommented:
Take OLEDB Data Source and set the SQL Command and pass parameter.

sp_test ?, ?

In parameters tab pass parameter
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.