Skip to content

SSIS Data Flow Task not reading columns correctly from stored procs?  3 possible solutions

Published by Concentra

Discover analytics solutions from Concentra


Concentra’s analytics and business intelligence teams turn information into insight to give you the edge from your data. Learn more.

Working as a BI Developer with the Microsoft stack of tools, I often handle complex business rules/functionality within stored procedures using temp tables and call it in SSIS within Execute SQL task (or) in OLE DB source components within the dataflow task depending on the requirement. As you know, most companies are slowly but surely upgrading from SQL Server 2008 to SQL Server 2012/2014 and this blog is for those who are facing some problems with reading column metadata when calling certain stored procedures in SSIS and discusses some possible solutions.

Scenario:

You may have come across the following errors in the OLE DB source component of SSIS 2008 & SSIS 2012 if a stored procedure contains Temporary tables (OR) Dynamic SQL which is driven by input parameters.

 “The metadata could not be determined because statement ‘SELECT * FROM #TempTable’ in procedure ‘P_Procedure1’ uses a temp table.”.

sandeep1

Figure 1: OLE DB Source Component Error message when the source contains temp table

“The metadata could not be determined because statement ‘EXEC (XX)’ in procedure XX contains dynamic SQL. Consider using the WITH RESULT SETS clause to explicitly describe the result set.”

sandeep2

Figure 2: OLE DB Source Component Error message when the source contains dynamic SQL

SSIS 2008 Options:

Here are 3 possible solutions to this problem that I can think of while calling stored procedures within the Data flow task in BIDS 2008.

1. Expose the column metadata through a dummy SQL statement at the beginning of the stored procedure and call it in SSIS OLE DB source component without the SET FMTONLY OFF option as shown in Figure 3.

2. Use SET FMTONLY OFF option to retrieve the column metadata along with the data & set the Delay Validation to ‘TRUE’ on the data flow task & Validate External Metadata to ‘False’ on OLE DB source component. Still, this approach will result in poor performance.

3. Use Table variables instead of Temporary tables as shown in Figure 3. This may perform slow.

SSIS 2012 Options:

So, all 3 approaches work fine SSIS 2008. Approaches 1 & 2 don’t work in SSIS 2012. You need to explicitily describe the result set metadata using WITH RESULT SETS clause in order for the Approaches 1 & 2 to work in SSIS 2012 as shown in Figure 3 below.

tabelfinalborder

 Summary:

On investigation, I found out that:

I hope this helps resolving column metadata detection issue you are facing while calling stored procedures within the data flow task in SSIS.