Does SSIS support creating centralized error handling package or DLL that can be used in various SSIS packages?

Question

I have a few SSIS packages that have common process for sending email alerts, error handling, file import retrigger etc. When they have an error (validation issue with data, or an actual OnError , connectivity issue etc) then they all do the same thing, write to exception log table. So in such scenarios we want to implement a centralized exception handling/ error logging mechanisms. Is that a possibility with SSIS and how can this be achieved?

Answer 1

Yes it is possible but depending on what you do it may not be easy. If you have common functionality that can be done in scripts then you can move that to standalone SSIS tasks or components. This requires coding an assembly, getting it signed and installing it on the SSIS server to the GAC. This is somewhat detailed and can be hard to do the first time but is the most flexible. This is how we communicate with our custom services from SSIS.

If you are talking about configuring your error handling all the same way with error flows then I don't think that can be done easily with custom components. I think you have to do that by hand.

Answer 2

Hi @Pranaya Rajalingari,

You may configure Logging in SQL Server Data Tools.

When you add the log to a package, you choose the log provider and the location of the log. The log provider specifies the format for the log data: for example, a SQL Server database or text file.

Integration Services includes the following log providers:

The Text File log provider, which writes log entries to ASCII text files in a comma-separated value (CSV) format. The default file name extension for this provider is .log.

• The SQL Server Profiler log provider, which writes traces that you can view using SQL Server Profiler. The default file name extension for this provider is .trc.
• The SQL Server log provider, which writes log entries to the sysssislog table in a SQL Server database. You may experience decreased performance if you log to a system database such as master or msdb. A good practice is to create a separate database for Integration Services logging.

The Windows Event log provider, which writes entries to the Application log in the Windows Event log on the local computer.

• The XML File log provider, which writes log files to an XML file. The default file name extension for this provider is .xml.

You can write log entries to the sysssislog table as mentioned.

Integration Services (SSIS) Logging

Regards，

Zoe Hui

---

If the answer is helpful, please click "Accept Answer" and upvote it.