在SSIS/DTSx中使用事务管理 Using Transaction in SSIS DTSx package

Categories: Database; Tagged with: ; @ December 9th, 2012 11:32

之前有研究过一个container中某一个component失败后的事务管理:http://liguoliang.com/2012/using-transaction-in-ssis/

今天测试某一行数据出错时的事务管理.

 

需求:  DataFlow转移数据时非0即1, 一旦遇到失败, 回滚到操作前的状态.

解决: 使用Transaction.

测试:

准备数据:

表1 Source: tbUser: userID, userName, emai, age(varchar)

表2, Destination: 结构与表1相似, 但age 类型为 int.

(用for loop insert一堆数据:

DECLARE @i int
SET @i = 0
WHILE @i < 1000000
BEGIN
    SET @i = @i + 1
    INSERT INTO guoliangDB.dbo.tbUser (userName, email) values (‘name’ + CAST(@i as varchar(8)), ’[email protected]’ + CAST(@i as varchar(8)))
END

 

制造一条问题数据:

update tbUser set age = ‘fakeage’ where userID = 980000;

 

设计DTSx:

image

在数据跑到980,000时会因为数据转换而出现错误:

image

运行结束, 错误信息:

OnError,,9/12/2012 11:17:58 AM,9/12/2012 11:17:58 AM,-1071636471,0x,SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available.  Source: “Microsoft SQL Server Native Client 10.0”  Hresult: 0x80004005  Description: “Invalid character value for cast specification”.

OnError,,9/12/2012 11:17:58 AM,9/12/2012 11:17:58 AM,-1071607780,0x,There was an error with input column “age” (54) on input “OLE DB Destination Input” (38). The column status returned was: “The value could not be converted because of a potential loss of data.”.

此时Select Destination的数量为: 979998. 剩余的数据不会再被导入.

修改Dataflow transaction属性为: required

在遇到错误之后, Select destination数量, 逐渐减少, 直至rollback到0.

结论: 如需要事务支持, require transaction.

<->



// Proudly powered by Apache, PHP, MySQL, WordPress, Bootstrap, etc,.