question

erlokeshsharma08 avatar image
erlokeshsharma08 asked

SET FMTONLY off

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?
ssisstored proceduresdata source
2 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

and I am using sql 2005
0 Likes 0 ·
Please ignore the query running slow option. It was due to some logic inside the stored proc itself
0 Likes 0 ·

1 Answer

· Write an Answer
erlokeshsharma08 avatar image
erlokeshsharma08 answered
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.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.