SSIS - BypassPrepare what does this mean ?

Question

Please look at SQL task, General > SQL Statement > BypassPrepare. It says that BypassPrepare indicates whether the task should prepare the query before executing it.

What is meant by "prepare the query before executing it". Why do we need to do this ?

Also, when I press "parse query" button for my SQL query, then I see this warning "this operation has no effect since bypassprepare property was set to true". What is the meaning of this and should I be concerned about it ?

Answer 1

Hi Greg,

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.

---

Regards, Dean Savović

Answer 2

Hi Dean,

So how does it matter whether parsing is done on SSIS side  or Database engine side. 

Thanks ,

Kirti

Answer 3

Hi Dean,

So how does it matter whether parsing is done on SSIS side  or Database engine side. 

Thanks ,

Kirti

If you didnt set it the query gets parsed at runtime only.

---

Please Mark This As Answer if it solved your issue
Please Vote This As Helpful if it helps to solve your issue
Visakh
----------------------------
My Wiki User Page
My MSDN Page
My Personal Blog
My Facebook Page

Answer 4

Is there any example scenarios that SSIS execute sql task cannot parse?