SSIS is a great tool to move data around. Sometimes it’s easier to use a stored proc to pull together some data before you do stuff with it.
To use a stored procedure you can use an OLE DB source (in a Data Flow task) SQL command.
Works great, but if it doesn’t and you get the error code 0xC02092B4, you will have to add to your stored procedure.
Just add the following 2 lines to the top of your stored procedure:
SET NOCOUNT ON
SET FMTONLY OFF
This will clear up the metadata issues with SSIS trying to understand what your stored procedures outputs.