Why is my Stored Procedure executed twice?

Yesterday morning I spent quite a few hours on a problem that almost made my crazy. It was a simple little SSIS package executing a stored procedure on my SQL and writing the data out to a disk file. I used an OLE DB Source in my Data Flow and the first surprise I had was this source task taking fairly long to execute my simple procedure. I started a Profiler trace and I could see that my stored procedure was executing TWICE! and hence taking double the time that is expected. Yes, I’ve turned all my package validations (DelayValidation/ValidateExternalMetadata) OFF and yet I see double execution of the procedure with specific duration in the Profiler.

This drove me crazy and I checked my package and procedure design quite a few times to get out of this until I stumbled on the SET FMTONLY options in SQL Server.
FMTONLY is a command that is used pretty heavily by SSIS (and many other apps for that matter) to gather the metadata of the command being executed without actually executing it. This provides fast response times since nothing is being executed, but in our case this is unhelpful. Here’s why.
Be careful when you are experimenting with SET FMTONLY. It can become very confusing very quickly if you have set FMTONLY to on and try to do other tasks. You cannot delete records for example from a table when FMTONLY is set to ON, you can’t truncate a table, or do DDL statements. The reason is because they commands aren’t actually being executed, they are just gathering the metadata from the commands. SET FMTONLY isn’t actually executing the command; it’s just getting the “Format Only” of the command results.
When I revisited my stored procedure, I realized that I have overridden the default option of SSIS and explicitly have SET FMTONLY OFF.

I’ve made this change because it was convenient for me to work in the BIDS GUI as this allowed me to fetch the column names and edit them if required before presenting them to the downstream package components. To know more about how this can make a difference, please go through the fantastic post here.

What I didn’t care of too much is how SSIS doing this in the background. This time SSIS engine is not just getting the “Format only” but the actual lists of columns and hence in turn actually running the stored procedure in the background. Having realized this now, I removed SET FMTONLY OFF from the OLE DB source and my problem is resolved! I obviously chose to get rid of the double executions of the stored procedures but this decision should be taken based on the requirement. It will be a tradeoff between the performance of the package versus the designer convenience while developing the package.

Hope this Helps!!

Author : Debarchan(MSFT) SQL Developer Engineer, Microsoft

Reviewed by : Snehadeep(MSFT), SQL Developer Technical Lead , Microsoft