How to use SQL Server Complex Stored Procedures in SSIS Data Flow Sources

This blog is going to show you how to use SQL Server complex stored procedure in SSIS Data Flow task. The Ole DB source connection will not allow direct execute command of stored procedure. This problem arise because of stored procedure do not expose metadata, especially when we use temp tables inside the stored procedure.

To solve this problem, before writing execute command of stored procedure, write a select query which will expose a metadata same as stored procedure output.

The following image shows the example.

Cheers!

Uma