SSIS 2008执行SQL输出参数映射datetime2问题

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

我正在尝试使用SSIS 2008中的执行SQL任务将存储过程输出参数映射到包变量。

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

包变量是SSIS类型DateTime,存储过程参数是SQL类型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.

SQL语句是EXEC GetCurrentDate @ CurrentDate =?在参数映射屏幕中,参数映射到包变量,并指定了Output和Data Type DBTIMESTAMP。

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.

[执行SQL任务]错误:执行查询“EXEC GetCurrentDate @ CurrentDate =?”失败,并显示以下错误:“分配给变量”User :: CurrentDate“的值的类型与当前变量类型不同。变量可能不同在执行期间改变类型。变量类型是严格的,除了Object类型的变量。“可能的失败原因:查询问题,“ResultSet”属性设置不正确,参数设置不正确或连接未正确建立。

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

如果我在正在运行的查询上运行跟踪,我会看到该类型被假定为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?

有谁知道为什么假设类型是datetime2?

Thanks

2 个解决方案

#1

Found the answer on a Micorsoft Connect bug report:

在Micorsoft Connect错误报告中找到答案:

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.

我们正在关闭这种情况,因为这是预期的行为,并且是新的sql datetime类型更改的结果。您正在使用本机oledb连接管理器进行sql任务,在COM互操作的过程中,我们使用VARIANT来保存值,防止数据丢失的唯一方法是将值存储为BSTR变体。如果将User :: dateParam更改为String类型它将起作用,或者您可以切换到使用托管连接管理器绕过COM互操作。

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

#2

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

尝试在SSIS任务中将inout / output参数指定为DATE而不是DBTIMESTAMP。

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

这肯定适用于我所研究的SSIS 2005包。

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.

同样值得看看这个链接,它涵盖了这个以及SSIS和日期的其他几个问题。

#1