Yesterday, we stumbled into an annoying bug of the RTM version of SQL Server 2005. A new version of a previous project of mine was being released with a modified SSIS package. One of the modifications consists of the usage of a mapped input parameter in an Execute SQL Task. It requires the BypassPrepare flag of that task to be changed to True to make this construct work. It was succesfully tested in development, acceptance and then, in production, we suddenly got the following error.
Executing the query "…" failed with the following error: "Syntax error, permission violation, or other nonspecific error". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
This error lists almost everything that can possibly go wrong with an Execute SQL Task without giving any indication what actually went wrong. Sigh.
At first I thought that the problem was caused by the fact that our production environment didn't had SP1 and the necessary hotfixes like it should have. That's an essential factor indeed, but there was more to the problem. The trouble with the RTM version is that it doesn’t take this BypassPrepare into account. Whether you set this flag to true or false in the development environment, if you look at the actual xml that represents the package, you won't find a trace of this setting. This was fixed in SP1 (as correctly answered on the forums).
What I didn't know was that our operational DBA did export and import the package with his local client (SSMS) on which he didn't have SP1 either. Similar to the behavior in Visual Studio, the setting gets lost with SSMS in RTM version. After a quick install and verification, everything worked smoothly.
In the upcoming SP2 it is announced that the default of this setting will be changed to True, meaning that SSIS won't prepare that statement unless you explicitly specify it do so. It could have saved us some trouble. Valuable lesson learned is to keep all environments, and also the clients, always up to date.
