SSIS stored procedure output

Question

I have a SQL Server stored procedure that updates 2 tables, and outputs (via a Select statement) the updated data. I want to execute this from SSIS and also capture the data output in SSIS to generate an Excel file. Exploring using OLE DB Source Editor and using parameters. OLE DB gives errors when I execute package (see below).

Answer 1

Based on the error message you provided, the problem is not from using a stored procedure as a data source. The error message is clear:

The metadata could not be determined because Statement "INSERT INTO dbo.tprogrameprice( ... " in procedure MS_IncreaseRenewalFee uses a temp table

1. First, you can try explicitly defining the metadata by adding WITH RESULTSET option as described in the following link: SSIS Package not wanting to fetch metadata of temporary table
2. Try converting you temp table into a global temp table as described in the following link: SQL SERVER 2012 – Fix – Error :11525 – The metadata could not be determined because statement uses a temp table

Helpful Links

• Using Stored Procedure As OLE DB Source In BIDS
• Using Stored Procedure in OLE DB Source of SSIS package