SQL 2016 - SSIS and complex stored procedure

Naomi 7,361 Reputation points
2022-08-22T20:10:36.08+00:00

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.

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,585 questions
SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,446 questions
0 comments No comments
{count} votes

Accepted answer
  1. Yitzhak Khabinsky 24,836 Reputation points
    2022-08-22T21:12:02.65+00:00

    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.


0 additional answers

Sort by: Most helpful