Hi Guys, Here is the question. On one of the ssis packages (already developed and I am working on some new requirement) we have a oledb source that gets the data using a stored proc and the command that is currently written in the command window is -- SET FMTONLY off exec stored proc I referred to this article
https://simonworth.wordpress.com/2009/08/14/ssis-using-stored-procedures-as-a-data-source/ which beautifully explains the reason for using this is that by default ssis set fmtonly to ON to return the metadata information even before the stored proc is run and returns more than 1 resultset. To avoid doing this we should set it to OFF. However I dont see it as a plausible reason espacially in SSIS because what we want is to get the metadata info before sp is being executed so that a new column is correctly mapped. Other thing I noticed is when FMTONLY is set to OFF the query takes forever to run and the BIDS window freezes up. What should be ideally done - ON or OFF?
I would answer this myself...We should under ideal circumstances avoid using fmtp off, because this would force the sp to run twice even at the developing stage. For details refer to the link that I pasted in the question itself.