How to resolve the metadata issue when using a stored procedure as source in SSIS?

So you want to use a stored procedure as source (EXECUTE dbo.usp_YourProcedure) in your SSIS data flow but all it gives you is some weird could not determine metadata error… That can be resolved with an easy step – RESULT SETS. (Which in this case helps, or rather tells, SSIS which column metadata to use.)

In your data flow OLEDB source, instead of writing:

EXECUTE dbo.usp_YourProcedure

You write:

EXECUTE dbo.usp_YourProcedure
WITH RESULT SETS
(
  ( -- Of course this should be your actual column list
    Column1 nvarchar(1)
    ,Column2 nvarchar(1)
    ,Column3 nvarchar(1)
  )
);