The Data Flow Source in the Data Flow tab extracts the metadata from the source to populate the column information. In the case of a stored procedure that uses dynamic SQL within, this can pose a problem in getting the metadata. Depending on the situation here is one way to get around the issue. Below is sample stored procedure that works with the pubs database. Comment the mentioned block in the stored procedure to see the difference in the SSIS package.
With the block commented, the Data Flow Source does not have any columns to extract and map. With the dummy block in place the Data Flow Source extract the required metadata to display columns. Note that the code block will not execute at runtime but it allows you to call a stored procedure with dynamic SQL in it from the Data Flow source in a SSIS package.
CREATE PROCEDURE [dbo].[GetAuthorsByState] -- Add the parameters for the stored procedure here @States varchar(10) AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; DECLARE @strSQL nvarchar(1000) -- Start of the block to enable the Data Flow source to work -- Comment this block to see the effect on the Data Flow source in the SSIS package IF 1 =2 BEGIN SELECT [au_id] ,[au_lname] ,[au_fname] ,[phone] ,[address] ,[city] ,[state] ,[zip] ,[contract] ,[DateAdded] FROM [dbo].[tblAuthors] END -- End of the block to enable the Data Flow source to work SET @strSQL = ' SELECT [au_id] ,[au_lname] ,[au_fname] ,[phone] ,[address] ,[city] ,[state] ,[zip] ,[contract] ,[DateAdded] FROM [dbo].[tblAuthors] where state = ''' + @States + '''' EXEC dbo.sp_executesql @strSQL END