Yet another couple of hours wasted searching a silly microsoft ssis bug, sorry, intended feature. I tried to use the Execute SQL Task in a package, that should simply execute a stored procedure with an output datetime parameter that should be mapped to a local datetime variable inside of the package. Simple enough, but it constantly failed throwing this error message:
[Execute SQL Task] Error: Executing the query “EXEC GetCurrentDate @CurrentDate=? ” failed with the following error: “The type of the value being assigned to variable “User::CurrentDate” differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object. “. Possible failure reasons: Problems with the query, “ResultSet” property not set correctly, parameters not set correctly, or connection not established correctly.
My first thought was that the datatype of the local variable I was mapping to was incorrect, but after trying to map to all available and somewhat meaningful datatypes I realised that this was not the problem. After some searching I finally found the answer on StackOverflow (where else?).
Excerpt from the Microsoft Connect Bug Reporting Pages:
We are closing this case as this is expected behaviour and is a result of the new sql datetime type change. You are using a native oledb connection manager for sql task, in the process of COM interop, we use VARIANT to hold the value and the only way to prevent data loss is to store the value as BSTR variant. If you change User::dateParam to String type it will work, or you can switch to use managed connection manager to bypass the COM interop.
Long story short: just change the variable type of your local package variable to string and it magically works.
