SSIS:默认记录OnError不适用于RetainSameConnection [英] SSIS: Default Logging OnError don't work with RetainSameConnection

问题描述

我在连接管理器中使用RetainSameConnection = true，因为我正在使用事务(如果成功/失败，则最后进行BEGIN TRANSACTION，COMMIT或ROLLBACK).

I'm using RetainSameConnection=true with my Connection Manager since I'm using transactions (BEGIN TRANSACTION, COMMIT or ROLLBACK in the end if success/failure).

我还使用默认的SQL Server日志记录提供程序，并选择了OnError事件(以及其他事件).

I'm also using the default SQL Server Logging Provider and selected the OnError event (and others too).

问题是我看不到sysssislog表中记录的OnError事件(该表存在并且已创建默认的插入SP)-我可以看到记录的其他类型的事件.

The problem is I can't see the OnError event logged in the sysssislog table (the table exists and the default insertion SP is created) - I can see other type of events logged.

使用SQL事件探查器，我可以检查是否已执行sp_ssis_addlogentry:

With the SQL profiler, I can check that the sp_ssis_addlogentry was executed:

exec sp_executesql N'exec sp_ssis_addlogentry @ P1，@ P2，@ P3，@ P4，@ P5， @ P6，@ P7，@ P8，@ P9，@ P10，@ P11'，N'@ P1 nvarchar(4000)，@ P2 nvarchar(4000)，@ P3 nvarchar(4000)，@ P4 nvarchar(4000)，@ P5 uniqueidentifier，@ P6 uniqueidentifier，@ P7 datetime2(7)，@ P8 datetime2(7)，@ P9 int，@ P10 varbinary(8000)，@ P11 nvarchar(4000)'， N'OnError'，N'EWOIU027013096'，N'ad \ oiu099'，N'Empty AF SINtemp'，'4CCEB32F-E884-483C-A02F-56D5C8438E44'， '15F585B3-AC6C-476F-8A2E-FC926438AC84'，'2012-07-12 14:46:15'，'2012-07-12 14:46:15'，0,0x， 违反主键 约束``CIRNAGF0p0_AF''.无法插入重复项 输入对象"dbo.TIRNAGF0_AF".

exec sp_executesql N'exec sp_ssis_addlogentry @P1, @P2, @P3, @P4, @P5, @P6, @P7, @P8, @P9, @P10, @P11',N'@P1 nvarchar(4000),@P2 nvarchar(4000),@P3 nvarchar(4000),@P4 nvarchar(4000),@P5 uniqueidentifier,@P6 uniqueidentifier,@P7 datetime2(7),@P8 datetime2(7),@P9 int,@P10 varbinary(8000),@P11 nvarchar(4000)', N'OnError',N'EWOIU027013096',N'ad\oiu099',N'Empty AF SINtemp','4CCEB32F-E884-483C-A02F-56D5C8438E44', '15F585B3-AC6C-476F-8A2E-FC926438AC84','2012-07-12 14:46:15','2012-07-12 14:46:15',0,0x, N'Violation of PRIMARY KEY constraint ''CIRNAGF0p0_AF''. Cannot insert duplicate key in object ''dbo.TIRNAGF0_AF''.'

但是没有插入OnError事件.

But the OnError event isn't inserted.

当我将RetainSameConnection属性更改为false时，它可以工作，但是我不能依靠我的Transactions SQL任务.

When I change the RetainSameConnection property to false, it works, but then I can't rely on my Transactions SQL Tasks.

注意:我什至尝试使用RetainSameConnection = false尝试创建另一个专门用于记录日志的连接管理器，但是没有用.

Note: I even tried to create another Connection Manager exclusively for Logging with RetainSameConnection=false, but it didn't worked.

我可以毫不费力地登录到文本文件，但是我们需要登录到表.

I can log without any hiccup to a text file, but we need to log to a table.

预先感谢

推荐答案

我无法重现您的问题.根据您的描述，我认为您的包裹看起来像这样. SO_retain OLE连接管理器指向我正在工作的数据库，并将RetainConnection属性设置为True.

I cannot reproduce your issue. Based on your description, I assume your package looks something like this. The SO_retain OLE Connection Manager points to the database where I'm working and have set the RetainConnection property to True.

我添加了事件OnError和OnPre/PostExecute的日志记录.我把包裹跑了两次.一次使用SO_retain连接管理器，一次使用SO_no_retain.

I have added logging for events OnError and OnPre/PostExecute. I ran the package twice. Once using the SO_retain connection manager and once with SO_no_retain.

执行后，我查询了日志以查看发生了什么. SELECT L.id, L.event, L.source, L.executionid FROM dbo.sysdtslog90 L ORDER BY 1;如果使用的是2008/2008R2，请将表修改为dbo.sysssislog.

After execution, I queried the log to see what had occurred. SELECT L.id, L.event, L.source, L.executionid FROM dbo.sysdtslog90 L ORDER BY 1; If you're using 2008/2008R2, modify the table to dbo.sysssislog.

我的结果是

1   PackageStart    so_Andre_LoggingTrxn    B2D17896-199F-4213-B800-E812CE95D45F
2   OnPreExecute    so_Andre_LoggingTrxn    B2D17896-199F-4213-B800-E812CE95D45F
3   OnPreExecute    Begin tran  B2D17896-199F-4213-B800-E812CE95D45F
13  OnPostExecute   Rollback    B2D17896-199F-4213-B800-E812CE95D45F
14  OnPostExecute   so_Andre_LoggingTrxn    B2D17896-199F-4213-B800-E812CE95D45F
15  PackageEnd  so_Andre_LoggingTrxn    B2D17896-199F-4213-B800-E812CE95D45F

16  PackageStart    so_Andre_LoggingTrxn    F6898ECA-46E7-4760-8885-898FADCBEFFD
17  OnPreExecute    so_Andre_LoggingTrxn    F6898ECA-46E7-4760-8885-898FADCBEFFD
18  OnPreExecute    Begin tran  F6898ECA-46E7-4760-8885-898FADCBEFFD
19  OnPostExecute   Begin tran  F6898ECA-46E7-4760-8885-898FADCBEFFD
20  OnPreExecute    Sequence Container  F6898ECA-46E7-4760-8885-898FADCBEFFD
21  OnPreExecute    Divide by zero  F6898ECA-46E7-4760-8885-898FADCBEFFD
22  OnError Divide by zero  F6898ECA-46E7-4760-8885-898FADCBEFFD
23  OnError Sequence Container  F6898ECA-46E7-4760-8885-898FADCBEFFD
24  OnError so_Andre_LoggingTrxn    F6898ECA-46E7-4760-8885-898FADCBEFFD
25  OnPostExecute   Divide by zero  F6898ECA-46E7-4760-8885-898FADCBEFFD
26  OnPostExecute   Sequence Container  F6898ECA-46E7-4760-8885-898FADCBEFFD
27  OnPreExecute    Rollback    F6898ECA-46E7-4760-8885-898FADCBEFFD
28  OnPostExecute   Rollback    F6898ECA-46E7-4760-8885-898FADCBEFFD
29  OnPostExecute   so_Andre_LoggingTrxn    F6898ECA-46E7-4760-8885-898FADCBEFFD
30  PackageEnd  so_Andre_LoggingTrxn    F6898ECA-46E7-4760-8885-898FADCBEFFD

正如您所看到的，事情正在发生，就像人们在交易中所期望的那样.在第一次执行中，程序包开始触发已记录的事件(PackageStart，OnPreExecute).源"Begin tran"的OnPreExecute是我们看到的最后一件事，直到触发"Rollback"的OnPostExecute.但是，ID之间存在差距.该缺口是交易中发生的所有插入.该事务将回滚，因此所有工作都将被撤消，唯一的指标是消耗的身份值.

As you can see, things are happening as one would expect in a transaction. In the first execution, the package begins firing events which are logged (PackageStart, OnPreExecute). The OnPreExecute for source "Begin tran" is the last thing we see until the OnPostExecute of "Rollback" fires. However, there is a gap in the IDs. That gap are all the inserts that occurred under the transaction. That transaction is rolled back so all of that work is undone and the only indicator is the consumed identity values.

当我更改日志记录以将SO_no_retain用于连接管理器时，我看到正在记录OnError事件.

When I change logging to use SO_no_retain for the connection manager, I see the OnError events being logged.

您正在查看日志中的第一组条目，但希望看到第二组.

You are seeing the first set of entries in your log but the desire is to see the second.

了解为什么如此是交易如何工作的基础.事务将工作陈述打包成单个的东西，这些东西全部将起作用，或者 all 将失败.在您的事务中，程序包正在发出sp_ssis_addlogentry命令.当前事务中没有办法说使这些语句比其他语句更持久"或将这些语句保留在当前事务之外".全部或全无，这是事务上下文中的唯一选项.

Understanding why this is so is fundamental to how transactions work. A transaction packages up statements of work into a single block of stuff that's all going to work or it's all going to fail. In your transaction, the package is issuing sp_ssis_addlogentry commands. There's no way inside the current transaction to say "make these statements more durable than the rest" or "persist these outside the current transaction." All or none, that's the only option within the context of a transaction.

鉴于此，您的选择是创建第二个专用于日志记录的连接管理器(如我在SO_no_retain CM中所示)或编写您自己的错误日志记录系统.我确实会提倡前者，因为它是本机行为.否则，您将花费大量时间

Given that, your options are either create a second connection manager dedicated to logging as I has shown with the SO_no_retain CM or write your own error logging system. I really, really would advocate the former as it's native behaviour. Otherwise, you're going to spend lots of time

这让我很惊讶，也许您没有意识到.您无需使用begin tran/commit/rollback模式. SSIS可以为您开箱即用.根据程序包的运行位置，将需要运行MSDTC(MS分布式事务处理协调器)，但总之，您告诉程序包您希望这些组件加入事务中，瞧，魔术发生了，您却没有不必管理它. SSIS中的每个任务都有一个TransactionOption属性.默认情况下，将其设置为受支持".

This one struck me that perhaps you weren't aware of it. You don't need to the begin tran /commit/rollback pattern. SSIS can do that out of the box for you. Depending on where the packages run, it will require the MSDTC (MS distributed transaction coordinator) to be running but in short, you tell the package that you'd like to have these components enlist in a transaction and voila, magic happens and you don't have to manage it. Every task in SSIS has a property for TransactionOption. By default, that is set to Supported.

TransactionOption设置

• 受支持的:如果存在交易，则该任务将加入其中.它不会开始交易.
• 必填.这将启动交易.如果已经存在，那么它将参加该交易
• 不需要:这不会启动交易，也不会加入正在进行的交易中.您可以使需要和不需要的对象尝试操纵相同的资源而陷入僵局，从而观察您的工作.

• Supported If a transaction exists, the task will enlist in it. It will not start a transaction.
• Required This will start a transaction. If one already exists, then it will enlist in that transaction
• NotRequired This will not start a transaction, nor will it enlist in an ongoing transaction. You can deadlock yourself by having required and not required objects attempting to manipulate the same resources so watch what you're doing.

鉴于上述软件包，我要做的是

Given the above package, what I would do is

1. 删除协调事务的执行SQL任务"，
2. 将RetainSameConnection属性保留为False
3. 在控制流级别(或序列容器)，我将TransactionOption设置为Required

完成，就是这样.当程序包运行时，工作"将回滚，但包括错误在内的日志记录仍将存在.老实说，我编写的大多数程序包看起来都是这样.我从来没有理由尝试控制自己的交易，因为我(也许愚蠢地)信任交易协调员来处理所有这些事情.在使用SSIS的7年中，我对MSDTC不处理它没有遇到麻烦.

Done, that's it. When the package runs, the "work" is rolled back but the logging, including errors, will be there. Quite honestly, most every package I write looks something like that. I've never had reason to attempt to control my own transactions as I, perhaps foolishly, trust the transaction coordinator to handle all of that. In 7 years of working with SSIS though, I haven't had troubles with the MSDTC not handling it.

这篇关于SSIS:默认记录OnError不适用于RetainSameConnection的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！