在SSIS/DTSx中使用事务管理 Using Transaction in SSIS DTSx package
Categories: Database; Tagged with: DTSX • ETL • SSIS • Transaction; @ 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@’ + CAST(@i as varchar(8)))
END
制造一条问题数据:
update tbUser set age = ‘fakeage’ where userID = 980000;
设计DTSx:
在数据跑到980,000时会因为数据转换而出现错误:
运行结束, 错误信息:
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.
SSIS Designer(Visual Studio 2005) cannot open package: Exception from HRESULT: 0x8007007E (System.Windows.Forms) <->