How to enable SSIS package logging for SQL Server

1.     Background

The purpose of this document is to describe how to enable SSIS package logging for SQL Server.

2.     Steps to enable logging for SQL Server in Packages :

                                 i.     For enabling the logging in packages, open your SQL Server integration project for the packages for which we need to enable logging.

                              ii.       Open the package e.g Fetch_FinanceData.dtsx  then right click on the Control flow area ==> Logging

                            iii.       Click on the logging, It will open the provider & log configuration window. In the provider type, select “SSIS log provider for SQL Server” and click on Add button.

 

                              iv.       It will create SSIS log provider for SQL Server. Add the SQL connection to the log provider.

                               v.      Select the check box for log provider and click on Details. It will show you the events for which we can enable logging. Select “OnError”, “OnPreExecute”, ‘OnPostExecute’ and “OnTaskFailed” and click ok

 

                             vi.       Enabling SQL server logging will create a table named ‘sysssislog’ and stored procedure named ‘sp_ssis_addlogentry’  that populates the package logging information into ‘sysssislog’.

Fig showing the logging data from sysssislog table

3.     Conclusion

           By using above steps, we can enable logging for SQL server in SSIS packages.