Logging In SSIS

Logging, as the name suggests, we need to log the events that are happening in our package. For example, we need to know which all tasks have executed successfully or have failed. In case of failure, what was the error message etc.

As per MS BOL SSIS provides a diverse set of log providers, and gives you the ability to create custom log providers. The Integration Services log providers can write log entries to text files, SQL Server Profiler, SQL Server, Windows Event Log, or XML files. This help in implementing logging in SSIS packages. With logging you can capture run-time information about a package, helping you audit and troubleshoot a package every time it is run.

Following are the different log providers:
1. Text File
2. SQL Server Profiler
3. SQL Server
4. Windows Event
5. XML File
Following are the easy steps one can follow to configure logging:

1. Create a SSIS package, To enable logging goto menu, select SSIS-> Logging…
2. On this new window select the Provider Type as “SSIS log provider for SQL Server”, click Add.
3. The log provider will be added, select by checking the check box. Now provide the connection where the log table should get created
4. Switch to the Details tab and select the events you want to log and audit. Click OK, save your SSIS package and your logging is enabled.
5. After this run your package and switch to  SSMS and check on object explorer that the table sysdtslog90 has been created on the database you added as a connection. Now you can query the sysdtslog90 table where the logs are stored.