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) ) );