There's no problem run sp, you have to do a few steps:
1. Create a new Data Flow Task.
2. Add OLE DB Source in DFT.
3. Put EXEC "your_sp_name" in SQL Command text.
4. Mark output columns.
5. Add OLE DB Destination and join with new table.
6. Join OLE DB Source with OLE DB destination.
If there are more then one select statements in your sp, it will show columns from the first one, but
it's not a big case.