All about Business Intelligence

Often when using parameters in an Execute SQL task the thing just does not understand me. Or at least not what I am trying to do with the parameters. It seems the SQL Native Client is even more dumb when it comes to passing parameters than the MS OLE DB Provider for SQLServer, though I have not done a thorough investigation to identify the dumbest of the two.
A workaround is to put the SQL statement as an expression in the SQLStatementSource property. But be careful… this has already cost me hours of unnecessary debugging. With a simple trick this waste of time can easily be avoided.

Consider a simple SQL statement like this and single mapped parameter:

insert into sometable
select ?

This returns with an error Possible failure reasons: Problems with the query, “ResultSet” property not set correctly, parameters not set correctly, or connection not established correctly.

To make it work: write the same SQL via the expression builder in the SQLStatementSource property but put the parameters directly where you need them. Notice you are building a string so everything is put in double quotes and the parameter is cast to a string. If the parameter would have been a string already, then extra single quotes would have been necessary to build correct SQL.

Evaluate the expression to check the result.

Now here is the time saver: put /* EXPRESSION */ in front of the SQL statement! When Visual Studio saves the package it will overwrite whatever is in the SQLStatementSource field in the properties pane with the evaluated expression. But… this, of course, looks like plain ordinary SQL. When - weeks later - you need to make an update to the SQL you might be tempted to update the SQL in the properties field directly and then wonder why your modification does not work. That was what happened to me… So now I consistently put the eye catcher /* EXPRESSION */ in every SQL statement fields that are in fact expressions.