From The MSDN Forums:
If you set this option to true then the preparing (i.e. parsing) is done by the database engine you are connecting to. If you set this option to false then the preparation is done by integration services package.
This option is available only for OLEDB type connections and was introduced because pacakge (sql task) cannot prepare/parse all the SQL commands that OLEDB database supports. Meaning that you would get error in parse phase and would not be able to execute the statement that is valid statement on OLEDB database but cannot be prepared/parsed by SQL task.
~Dean Savović
I'm just going to be honest and say that I don't understand this answer. I am a networking person, and am only (slowly) learning data-warehousing.
My question is 2-part:
1. When Dean says "prepare/parse," what parsing/preparing is going on?
2. OLEDB is an API as far as I understand. Are requests to an OLEDB interface serialized? If not, what would prevent Visual Studio + SSIS from sending strings containing supported commands?
I feel like I'm missing something crucial about OLEDB connections here. I've found 2 other SO questions on this topic, but both have received answers parroting the quote above.
First of all, it is important to remember that BypassPrepare is a property of Execute SQL Task, not a database connection.
The property allows you to toggle design-time SQL syntax check, which can be done for you by VS automatically when you are authoring a package. If you turn the check on and try to set a syntactically incorrect SQL statement, the component editor will tell you everything it thinks about you in general, and your SQL coding skills in particular.
With the syntax check disabled, you can feed absolutely any kind of hogwash to the task, and nothing will happen until you will try to actually execute it. In that case, you will have a run-time exception.
If you, like me and many other developers, first concoct your SQL statements in a full-fledged SQL editor like SSMS or similar, you probably don't need to touch the option at all, and leave this syntax check disabled by default.
Another issue with this property is that it raises false alarms every time you are trying to save parameterised SQL. I'm not sure if it works correctly with ADO.Net, but in case of OLEDB connection parameters are designated by question marks, and the built-in syntax checker appears to be blissfully unaware of this part of component's functionality.
If you were thinking whether SSIS switches to prepared statements with this property set to False, AFAIK it doesn't do that. In ETL, a typical query usually runs once and grabs either most or all of table's rows. There is no gain to be obtained by preparing such a statement before execution. Having said that, I might actually be wrong; one only needs to set up a Profiler / XEvents trace to verify this.
My advice: leave it at its default value.