Many users have issues when trying to use a stored procedure in an OLE DB Source component. Here are some tips:
- Ensure that there is a final select statement at the end of the stored procedure to ensure the correct resultset is returned
- At the top of the stored procedure as part of the procedure code, add the following line: SET NOCOUNT ON
- At the top of the OLE DB Source SQL statement and before you issue the EXEC YourSproc line, add the following: SET FMTONLY OFF.
This should ensure that when you click on the column mapping tab, the columns are correctly displayed.
Also another approach is to use a table variable in the stored proc. Then at the end, select from that table variable.