SSIS + Temp Table

Question

I am having a stored proc which return 2 records and which contains a select statement from a temp table, So when i used this stored proc inside a OLEDB Source in SSIS and just trying to insert the set of records into a destination SQL table using OLEDB destination.

Its throwing a ERROR at source side saying :

[OLE DB Source [1661]] Error: A rowset based on the SQL command was not returned by the OLE DB provider.

Moreover tried with set nocount on and with SET FMTONLY OFF

But of no avail...........

Need help in this.........

Answer 1

I had a similar problem before and it was due to SSIS not being able to determine the output for dynamic SQL. I solved it by including a "fake" Select statement at the top of the query that matched the expected output. i.e.

CREATE PROCEDURE MyStoredProc
AS

    SELECT 1 As Field1, 2 As Field2, 3 As Field3 -- list of fields of appropriate type
    WHERE 1 = 0 -- so nothing is actually returned

    -- Real SQL goes here.

GO

Answer 2

Please use same connection and , make delay validation=true