How and when to use the Execute SQL task PARSE QUERY button in SSIS
A short blog explaining how the Parse Query property and BypassPrepare property for an SSIS task are related.

Posted by Andy Brown on 18 September 2015

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

The Parse Query button for an SSIS Execute SQL task

In answer to a question from a recent course: what does the Parse Query button do?

The Parse Query button

The Parse Query button for an Execute SQL task.

When you click on this button, SSIS displays this message:

BypassPrepare property message

You can't proceed while the BypassPrepare property is set to True.

So you could now set this property to False:

Property set to false

The BypassPrepare property is now set to False.

And now when you parse the query, it works:

Query parsed OK message

Because this was valid SQL, the query now parses correctly.

 

The obvious question is: why did we have to change the BypassPrepare property?  The answer is that by default SSIS will use its own logic to prepare a SQL statement, whereas if you set the property to False the SQL statement will be passed to the SQL Server database engine for checking.

If you are using parameterised queries and change the BypassPrepare statement to False, your package will fail.

Practical Advice

Given all this, there seem to be two sensible courses of action.  Either:

  • Type your SQL into Management Studio, parse it there and only then copy it into the SSIS Execute SQL task; or
  • Set the BypassPrepare property to False, parse your query and then set it back to True!

Quite why Microsoft couldn't do all this behind the scenes for you is beyond this owl ... 

 

This blog has 0 threads Add post