When a data source calls a stored procedure, you often don't see the column names exposed for mapping in the dataflow designer. There are a number of work-arounds involving SET FMTONLY OFF or setting up a dummy select statement early in the stored procedure,
non of which are entirely satisfactory.
Perhaps the SSIS developers could learn something from SSRS here, after the data from a datasource is previewed, the column names are available in the Report Desginer no matter how complex the stored procedure might be.
I would have thought that when you preview the data from the strored procedure in BIDs, that all the required information about the column names and data types returned by the stored procedure could be captured and made available in the data flow designer.
View Complete Post