SSIS中，用Lookup Transformation查找维度表，查入新数据报错

Question

在SSIS中，我用Lookup Transformation查找唯度表来获取外键，再插入到事实表中。如果记录是维度表中不存在，就把新的记录插进去，再获取外键。

我用的是Partical cache模式，然后自定义查询的语句，像这样：EXEC [dbo].[sp_Get_OS_Id] @OSVersion = ?，存储过程中就是查找，如果没有插入，再返回查找到的记录。

我现在有这么一个问题，当新记录来的时候，第一条总是查找失败，之后的就可以。所以说，对于每个新的OSVersion，总会有一条记录失败。我开始认为是锁的问题，所以我就在事务中获取共享锁。然后第一条语句获取排他锁来同步。大概的逻辑如下。但是还是不行。请指教，谢谢

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ

BEGIN TRAN

SELECT @si_os_id = si_os_id

FROM [dbo].[dim_os] WITH ( XLOCK )

WHERE vc_OSVersion = @OSVersion ;

IF @@ROWCOUNT = 0

BEGIN

INSERT INTO dbo.dim_os(vc_OSName,vc_OSFullName,vc_OSVersion)VALUES (@OSName,'**Unknown',@OSVersion)

SELECT @si_os_id = si_os_id

FROM [dbo].[dim_os]

WHERE [vc_OSVersion] = @OSVersion ;

END

COMMIT TRAN

Answer 1

What's the error?

Answer 2

I just have an error log file to store the error rows from the lookup component, so I can see the error rows. When I execute the stored procedure manully using the os version from the error log, it works. I can get the right row and there is no error.

Answer 3

Didn't get it, can you post same sample data (right and wrong)?

Answer 4

Please try "set nocount on".

---

想不想时已是想，不如不想都不想。

Answer 5

Great. Thanks, your method works. Could you please tell me how do you figure out this solution. And should I remove the isolation level and xlock

thanks a lot. :)

Answer 6

如果没有set nocount on，insert/delete之类的操作会返回DONE_IN_PROC消息，然后SSIS就会以为是一个空结果集。

---

想不想时已是想，不如不想都不想。