CODEWITHSUNDEEP
sqlsql-serverevent-handlingssis
CuriousBuddy
CuriousBuddy

Reputation: 197

SSIS OnError EventHandler does create a Entry in the table but Inserted Row not visible

I am building a SSIS Package and trying to implement Event Handlers.

I want that whenever an error occurs anywhere in the complete process, a log entry should be made in the table with System::SourceName, System::ErrorDescription , System::ErrorCode and System::ExecutionInstanceGUID.

For this, I have created a table as shown below:

CREATE TABLE [dbo].[ErrorLog]([LogID] [int] IDENTITY(1,1) NOT NULL,
[ExecutionID] [nvarchar](255) NULL,[SourceName] [varchar](255) NOT NULL,
[Message] [nvarchar](max) NOT NULL,
[MessageCode] [int] NOT NULL,
[LogDateTime] [datetime] NOT NULL
) ON [PRIMARY]

I have created an OnError Event Handler at the Package level. The Sql query used is INSERT INTO [ErrorLog]([ExecutionID],[SourceName],[Message],[MessageCode],
[LogDateTime]) VALUES(?,?,?,?,getdate())

The issue I am facing is, whenever, an Error occurs anywhere in the Package the Event Handler does goes Green showing its invocation but the table in the database does not show the entry.

However, I am sure that a row does gets inserted each time an error occurs as whenever I create a new row in that table manually, a new row gets added with the next identity value for the column [LogID] .

For Example, suppose the value in the LogID Column is 74 and an error occurs in the package, NO row gets added and when I insert a record manually in the ErrorLog Table the identity inserted is 76.

Not sure what is going wrong.

I am in this fix and would really appreciate any help.

Thanks Mohit

Upvotes: 1

Views: 2292

Answers (1)

Diego
Diego

Reputation: 36146

This looks like the insert is being rolled back.

Do you have transaction control on your package?

To test I would set the transaction support property on the component on the onerror even and the task that triggered the error to NotSupported. It indicates the task won't participate on any transaction initiated by the package or any other parent task.

Upvotes: 1

Related Questions