SSIS BypassPrepare and DelayValidation Comparison

     The sample package referenced in this post can be downloaded here:

     I blogged about the DelayValidation property of control flow tasks and packages a few months back and was just asked the difference between this property and the BypassPrepare property.  The biggest and most notable difference is that DelayValidation is present in all tasks and packages while the BypassPrepare property is only available in Execute SQL tasks.  The BypassPrepare property prepares a statement to be sent to the connection provider defined in the task and this will be performed ONLY at runtime, another distinction between this property and DelayValidation. 

     The default value for this property is set to True, since SSIS 2005 SP2, so queries contained in the task will not be prepared before being sent to the connection.  This can have some effect on performance; I will post a follow up with a sample package to demonstrate the extent to which performance is affected.  So the question arises “Why would I want to bypass preparation of a query“.  The answer is very simple and straight forward, if you are using a parameterized query in your execute SQL task you must insure this property is set True or the task will fail.

     To demonstrate this behavior of this property:

  1. Create a package 
  2. Add two execute SQL tasks
  3. Task 1 name Bypass True
  4. Task 2 name Bypass False
  5. Create a connection manager pointing towards any instance of SQL
  6. Create a variable called “dt” of data type string with a value of GETDATE()
  7. Bypass True query statement “SELECT GETDATE()”
  8. Bypass False query statement “SELECT ?”
  9. Map the “dt” variable as an input variable to the Bypass False task as a VARCHAR data type
  10. Set the BypassPrepare property of the Bypass True task to True
  11. Set the BypassPrepare property of the Bypass False task to False
  12. Connect the On Success precedence constraint from the Bypass True to the Bypass False task

The package should look similar to this:

BypassPackage

          Executing the package will demonstrate that the Bypass True succeeds while the Bypass False fails.  Changing the Bypass False BypassPrepare property to True and re-executing the package will result in the task succeeding.  The reason is that a parameterized statement cannot be prepared. 

     To further delineate the difference between the two properties also note that the Bypass False task successfully validated, but still failed once the query was attempted to prepare.  That is because SSIS recognizes parameterized queries in this context while query preparation does not.

BypassProgress

     You can review how to create parameterized queries in SSIS on my blog here and query variables here and parameterized queries here.  You can also find more information on the BypassPrepare property on MSDN under Parameters and Return Codes.

     As always the sample package is available for download here.

Leave a Reply


Hit Counter provided by Sign Holders