Quick Description: An SSIS package which populates an SSIS variable of type DateTime and passes the variable as a parameter to a stored procedure (expecting DateTime) using OLE DB blows up with the error below.
[Execute SQL Task] Error: Executing the query "exec <procedure>..." failed with the following error: "Invalid time format". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
One Solution: In my situation it turned out to be another run-in with my nemesis, the deeply confusing datatype mappings in SSIS (2012 in my case). According to this Microsoft article the mapping for SQL Server DateTime is DT_DBTIMESTAMP. For an Execute SQL task calling a stored procedure (using OLE DB) expecting a DateTime parameter, however, this blows up with the error above. Setting the parameter to type DATE (not DT_DATE), fixed the problem for me, and the parameter is populated with a full Date/Time (including fractional seconds).