May 29, 2015 at 2:03 pm
On SQL Server 2008 R2 some SSIS packages created and regularly scheduled to run have worked perfectly for over a year.
These packages have been upgraded on our new 2014 server ... now some fail with the issue that the Stored Procedure executed in a Data Flow task cannot handle the Temp tables that are used. Error is basically that it cannot get the metadata for the columns of the temp tables.
Since these packages do actively work in 2008 R2 ... I am thinking there is some way to get them to work in 2014 also.
Does anyone know what the trick might be?
Thank you in advance for your ideas or solutions.
May 30, 2015 at 3:46 am
nshockley (5/29/2015)
On SQL Server 2008 R2 some SSIS packages created and regularly scheduled to run have worked perfectly for over a year.These packages have been upgraded on our new 2014 server ... now some fail with the issue that the Stored Procedure executed in a Data Flow task cannot handle the Temp tables that are used. Error is basically that it cannot get the metadata for the columns of the temp tables.
Since these packages do actively work in 2008 R2 ... I am thinking there is some way to get them to work in 2014 also.
Does anyone know what the trick might be?
Thank you in advance for your ideas or solutions.
Things changed in SSIS 2012 and later and the trick is to use WITH RESULT SETS when calling your proc. See an example here.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
June 5, 2015 at 12:15 pm
The way metadata is returned in SQL Server 2012/2014 is different than SQL Server 2008 R2.
If you are looking for the quick fix and something that works than change the PROVIDER SQLNCLI10.1/SQNNCLI11.1 to SQL generic provider SQLOLEDB.1 in the package connection manager properties and config file if using one.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply