SSIS包中CASE与NVARCHAR的恩怨

今天在修改SSIS包时，遇到一个很莫名的情况：

将SQL语句

UPDATE [dbo].[Employee]
   SET DateOfChange = CASE WHEN (? = ?) THEN ? ELSE GETDATE() END
 WHERE [SerialNumber] = ?

填入OLE DB Command 组件中，运行后一直报错

“

[OLE DB Command [2015]] Error: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80040E21.
An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 10.0"  Hresult: 0x80040E21  Description: "Invalid character value for cast specification".
[OLE DB Command [2015]] Error: There was an error with input column "ServiceStatus2" (2087) on input "OLE DB Command Input" (2020). The column status returned was: "The value could not be converted because of a potential loss of data.".

”

之前我也有用过CASE语句，并顺利通过。仔细检查当中的不同，发现这次CASE WHEN 后的条件语句中数据类型是NVARCHAR，而之前是INT，问题就出在这里。并且微软也提示你“The value could not be converted because of a potential loss of data.” 值不能转换，因为可能丢失数据。

寻找解决方案的过程中，我尝试用CAST,SUBSTRING等在源头进行修改，结果都告失败。最靠谱的是再增加一个LOOKUP组件，用它来比较，就可顺利通过。

附上一幅图，其中Lookup SerialNumber And ServiceStatus 就是后来增加的。

转载于:https://www.cnblogs.com/MikeYao/archive/2011/03/09/1978577.html