Execute SQL Task, ADO.NET provider, parameters and result set - a how not to

Talk about a needle in a haystack. I began working on my own version of Andy Leonard's SSIS Instrumentation framework and in particular, I was trying to load some values into a recordset using a simple execute sql task with an ADO.NET connection type and some basic parameters. It shouldn't have been this hard and I should have looked at documentation sooner. Let this blog post serve as the quick reference guide to all the stupid mistakes you can make and how to resolve them.

The initial failure message was
[Execute SQL Task] Error: Executing the query "SELECT..." failed with the following error: "Failed to create an IDataAdapter object. This provider may not be fully supported with the Execute SQL Task. Error message 'Failed to convert parameter value from a DateTime to a Int32.'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.


Seems easy enough, I was trying to punch System::StartTime in as a parameter. I stripped my parameterized query down to just integer types (a variety of row counts)
[Execute SQL Task] Error: Executing the query "SELECT..." failed with the following error: "Failed to create an IDataAdapter object. This provider may not be fully supported with the Execute SQL Task. Error message 'Incorrect syntax near ','.'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Well that was silly, I had an invalid query "SELECT , @param1" isn't valid SQL. I corrected the syntax of the query and ran it with great success. Next step, start adding back the variables in the System namespace. First up, TaskName. Care to guess what happens when you forget to map a value to a parameter placeholder? If you said
[Execute SQL Task] Error: Executing the query "SELECT..." failed with the following error: "Failed to create an IDataAdapter object. This provider may not be fully supported with the Execute SQL Task. Error message 'Must declare the scalar variable "@TaskName".'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

you'd be absosmurfly correct. By the way, the converse is not an issue. The ADO.NET provider has no qualms about being passed parameters it doesn't use or have a placeholder for.

What was the root cause of my initial problem? Don't forget to set the value in the "Data Type" column. That did explain why SSIS thought it needed to convert a datetime to an int32.