The purpose of this document is to describe how to enable SSIS package logging for SQL Server.
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
By using above steps, we can enable logging for SQL server in SSIS packages.