How to enable logging in a SSIS package?

/ deyvidwilliam

SQL Server Integration Services includes log capabilities that are very flexible and easy to implement. Logging is extremely useful and allows us to capture run-time information about a package, helping to audit and troubleshoot it every time it is run. For example, a log can capture the name of the operator who ran the package and the time the package began and finished.

Logs are associated with packages and are configured at the package level. We can activate logging on the whole package, or choose to enable logging on any individual task or container within the package. Each of them, task or container, can log information to any package log (Text files, database table, etc.).

Basically, to enable logging, we have to choose the log provider, the location of the log and the predefined events we want to log. To do that, we are going to use the Configure SSIS Logs dialog box in SQL Server Data Tools.

Let’s go step by step.

  1. In SQL Server Data Tools, open the Integration Services project that contains the package you want.
  2. On theSSIS menu, click Logging.s1
  3. Select a log provider in theProvider type list, and then click Add.
  4. In theConfiguration column, select a connection manager or click <New connection> to create a new connection manager of the appropriate type for the log provider.s2
  5. On theDetails tab, select Events to log all log entries and click OK.s3

Pretty simple, don’t you think?

I prefer to log the events in a SQL Server database, that’s why I chose the “SSIS log provider for SQL Server”. It writes the log entries to the sysssislog table in a chosen SQL Server database. But we have other options:

  • Text File log provider, which writes log entries to ASCII text files in a comma-separated value (CSV) format.
  • SQL Server Profiler log provider, which writes traces that you can view using SQL Server Profiler.
  • Windows Event log provider, which writes entries to the Application log in the Windows Event log on the local computer.
  • XML File log provider, which writes log files to an XML file.

 

Auditing and troubleshooting your SSIS packages has never been so easy.
Go check your solutions and enable this option!

[Deyvid W A Evaristo]

Source: https://docs.microsoft.com/en-us/sql/integration-services/performance/integration-services-ssis-logging

Leave a comment