SSIS: Default Logging OnError don't work with RetainSameConnection
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).
I'm also using the default SQL Server Logging Provider and selected the OnError event (and others too).
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.
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, N'Violation of PRIMARY KEY constraint ''CIRNAGF0p0_AF''. Cannot insert duplicate key in object ''dbo.TIRNAGF0_AF''.'
But the OnError event isn't inserted.
When I change the RetainSameConnection property to false, it works, but then I can't rely on my Transactions SQL Tasks.
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.
Thanks in advance
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.
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.
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.
My results were
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
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.
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.
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.
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
A third option
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.
- 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
- remove the Execute SQL Tasks that coordinate transaction,
- leave the RetainSameConnection property as False
- At the control flow level (or Sequence Container) I would set the TransactionOption to Required
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.