BypassPrepare property in execute sql task in SSIS

This option is available only for OLE DB connection and helps us to specify that SQL statement prepared or not. “True” skips preparation, “False” prepares the SQL statement before running it. In other words, we can say that this property provides the ability to bypass the prepare command for OLE DB connections because prepare does not support some Transact-SQL keywords, if we are using a parameterized query in our execute SQL task we must ensure this property is set True or the task will fail.

By default this value is true.

                             

If we select other than OLEDB connection type then BypassPrepare property is disabled. See below.

      

This is disabled.

For the butter understanding let’s example. Here we will see two example

1.       BypassPrepare value is true

2.       BypassPrepare value is false

BypassPrepare value is true

Taking Execute SQL task.

     

   

Configuring connection manager.

Here I am creating a package to get date and time.

       

       

       

See the other configuration.

 

          

Here I am creating a variable for parameter mapping.

         

       

Now doing the parameter mapping.

       

             

Storing the result set in a variable.

            

          

Now I am taking a script task to show the result set.

       

         

The package is ready to run.

                      
  

Now I am running this package.

                  

           

Get the expected result.

BypassPrepare value is true

Now I am setting the BypassPrepare value is false

Let’s see

           

           

Now I am executing this package.

OMG, my package gets failed.

           

 

Let’s see why our package gets failed.

Go to the progress table

              

     

[Execute SQL Task] Error: Executing the query "SELECT ?" failed with the following error: "The parameter type for '@P1' cannot be uniquely deduced; two possibilities are 'sql_variant' and 'XML'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Conclusion: If we set this option to true then the preparing (parsing) is done by the database engine. We are connecting to. If we set this option to false then the preparation is done by integration services (SSIS) package.