The metadata could not be determined because statement 'INSERT INTO #temp_display () values () uses temp table

Question

I have upgraded the SSIS package from 2008 to 2014.Also, I have changed the provider value to "Provider=SQLNCLI11.1 ". When I run I got error like

OLE_SOURCE_RECORDS [9]] Error: SSIS Error Code DTS_E_OLEDBERROR.
An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft SQL Server Native Client 11.0"
Hresult: 0x80004005 Description: "The metadata could not be determined because statement 'INSERT INTO #temptest ( [a],[b]) values (0,'b' in procedure SPtest' uses a temp table.".

I researched and found we need to use With result set instead of temp table.But I need to modify all the sql query in SP.

Is there is any way we can run package without changing Stored procedure.

Answer 1

If you want to use local temporary table in SSIS you should be aware of two settings:

1) RetainSameConnection property to True

Local temporary table is visible only in current session. So you need to retain connection between components.

2) ValidateExternalMetadata to False