I am calling a stored procedure from an OLE DB data source in SSIS. Why doesn't SSIS see changes I have made to the stored procedure? It seems like metadata about the stored procedure is cached somewhere. Is it cached? If so how do I force SSIS to see the changes I made to the stored procedure?
A:
It's possible the problem isn't that SSIS isn't seeing your changes; but rather that what you're changing isn't visible to SSIS.
To clarify that very confusing answer, try this: drag an OLE DB data source onto some data flow. Hook it up to the same stored procedure, with the same parameters, etc. Check the metadata for the output columns.
If they are what you expected, then the problem is SSIS not seeing your changes. I'll be surprised if this is the answer - if anything, SSIS is too picky about seeing changes in metadata (especially in the 2005 version).
If you're not seeing the metadata you expected, then you'll have to edit your post with more detail on your stored procedure. But I can say in general that neither SSIS nor any other piece of software can determine the shape of your resultset in all cases. I believe they use the SET FMTONLY command to arrange that queries "don't really" execute. Instead, they are passed NULL for all their parameters. The result set that occurs when all your parameters are NULL or default values is the one that SSIS will see. If you have a complicated SP that returns result sets of different shapes based on input, then this could be the problem.
John Saunders
2009-04-25 12:34:38
A:
To regenerate the metadata, simply delete the flow pipe (red and green), and re-add them. This is the easiest solution I have found to this problem.
Devtron
2009-05-15 20:58:15