SQL 2016 - SSIS and complex stored procedure

Question

Hi everybody,

I'm trying to execute a complex stored procedure that may return different results depending on parameters (e.g. either count of rows or actual result) and it uses several temp table created on the fly for simplicity.

When I try to debug the package I'm getting this error:

TITLE: Package Validation Error

------------------------------

Package Validation Error

------------------------------

ADDITIONAL INFORMATION:

Error at Data Flow Task [OLE DB Source [106]]: 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 'WITH newAndChangedRows AS
(
SELECT
cte.person_id
, cte.internal_control_nbr' in procedure 'usp_File_Extract_800_DR_ZIP_CHG' uses a temp table.".

Error at Data Flow Task [OLE DB Source [106]]: Unable to retrieve column information from the data source. Make sure your target table in the database is available.

Error at Data Flow Task [SSIS.Pipeline]: "OLE DB Source" failed validation and returned validation status "VS_ISBROKEN".

Error at Data Flow Task [SSIS.Pipeline]: One or more component failed validation.

Error at Data Flow Task: There were errors during task validation.

--------------------------------------------------------

Is there any workaround or I'm at the dead end? The data flow task uses sql statement to execute this procedure.

Answer 1

Hi @Naomi ,

a complex stored procedure that may return different results depending on parameters (e.g. either count of rows or actual result

It is a poor design of a stored procedure. Any function or stored procedure should have the same parameters set and the same output, aka input/output.

Additionally, SSIS is metadata driven. So, such SP will not work in SSIS Execute SQL Task.