Because statement Select * in Procedure ABC uses a temp table..
Error at Data flow Task [OLEDB Source[31]]:Unable to retrieve column information from the data source . Make sure your target table in the database is available.
Can you help me with this error ?
When I ran the procedure from outside its working fine but I am unable to run through SSIS package.
If you are able to use table variables instead of temp tables, that will provide the meta data.
There are other workarounds - one for example is to use SET FMTONLY ON, run the query once, and then SET FMTONLY OFF to get the results. That is more executions, and if I am not mistaken, SET FMTONLY is deprecated.
There is another workaround described here see the last Wacky method. I haven't tried it, so I don't know if that works.