CODEWITHSUNDEEP
sql-servercsvssis
Prashanthi
Prashanthi

Reputation: 19

SSIS error handling for multiple CSV tables into one SQL table

I was asked to load 15 different CSV into 15 SQL tables. I am using truncate and load into tables with 15 different packages in one solution. But here the requirement is the client wants to load all the error details in one single error SQL table. They want a generic table where it stores from which package the error came, which row, where it is failing, which day it failed etc.

Can someone guide me on this?

Upvotes: 0

Views: 204

Answers (1)

Amira Bedhiafi
Amira Bedhiafi

Reputation: 1

You can use the event handlers in each package and store the errors in an audit database.

enter image description here

There are two sections in every SSIS Event Handlers:

Executable: You can use this it to select the Task, Containers on which you want to apply Event handling in SSIS.

Event Handler: has all the event. You can select the event that you want to handle.

You will choose the Event Handler OnError:

enter image description here

Then create a table in SQL Server under schema Audit for example :

CREATE SCHEMA Audit;

CREATE TABLE Audit.PackageError
(
 Pk_ID NVARCHAR(100),
 Machine_Name Nvarchar(200),
 Package_Name Nvarchar(200),
 Task_Name Nvarchar(200),
 Error_Code INT,
 Error_Description Nvarchar(Max),
 Dated Datetime
)

After selecting the OnError for the Package, add an Execute SQL Task to the Event Handlers :

enter image description here

In the Execute SQL Task, configure the database connection to the your audit database and setup the Expression as shown below :

enter image description here

Upvotes: 1

Related Questions