I have now run into this problem several times, so I thought I would write it down.
Just had a colleague come to me with an issue of the SQL Command task not returning the data it should have.
Looking at his stored proc, everything seemed fine. The proc took in a UniqueIdentifier and returned a SELECT from a table.
The SQL Command task in SSIS passed a GUID and put the result set into an object variable.
However, by watching Profiler, we captured that the value being sent to the stored procedure parameter from the SQL Command task was not the GUID that was being processed in SSIS.
So on the parameter mapping tab we changed the data type of the parameter from GUID to VARCHAR(70).
After that the SQL Command returned the rows that were expected.
I hope this helps someone out there