1

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.

Overcrash007
  • 11
  • 2
  • 9

1 Answers1

0

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

Lukasz Szozda
  • 162,964
  • 23
  • 234
  • 275
  • It doesnot work . I am getting the same error.I am getting the connection string dynamically from varaible. – Overcrash007 Aug 07 '17 at 19:44
  • @Overcrash007 Please post your SSIS package with description. – Lukasz Szozda Aug 08 '17 at 05:10
  • package will dynamically take the connection string from varriable.After upgrading ssis package from 2008 to 2014, I try to run package it calls stored procedure which uses #temporary table but got above shown error. Whats the issue with temp table after upgrading and how can i solve it. – Overcrash007 Aug 08 '17 at 13:17
  • Found change temp table to table variable in stored procedure or execute stored procedure with result set. – Overcrash007 Aug 11 '17 at 18:29