SSIS 2008 Execute SQL output parameter mapping datetime2 problem

Question

Hi, I'm trying to use an Execute SQL Task in SSIS 2008 to map a store procedure output parameter to a package variable.

The package variable is SSIS type DateTime and the store procedure parameter is SQL type DATETIME.

The SQL Statement is EXEC GetCurrentDate @CurrentDate=? and in the parameter mapping screen, the parameter is mapped to the package variable with direction Output and Data Type DBTIMESTAMP specified.

When I run the package I get the following error:

[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.

If I run a trace on the query being run I see the type is being assumed as datetime2:

declare @p3 datetime2(7)
set @p3=NULL
exec sp_executesql N'EXEC GetCurrentDate @CurrentDate=@P1 ',N'@P1 datetime2(7) OUTPUT',@p3 output
select @p3

Does anyone know why it is assuming the type is datetime2?

Thanks

Answer 1

Try specifying the inout/output parameters as DATE rather than DBTIMESTAMP in the SSIS task.

This certainly works in SSIS 2005 packages I've worked on.

It's also worth taking a look at this link, which covers this as well as a couple of other issues with SSIS and dates.

Answer 2

Found the answer on a Micorsoft Connect bug report:

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.

http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=307835