SQL Server Integration Services - Logging in Packages

In this post, I am going to explain about logging in SSIS.

We can maintain log about  package & system informations ,

various events for the container can also be logged as shown in below image:

We can enable logging in two ways as shown in below two images.

Right-click on Control Flow tab

We can capture logged information in text files,XML files,trace files,SQL Server tables and also through windows event viewer.

Example 1: SSIS log provider for Text files

Step 1:

Select Provider type: SSIS log provider for Text files and click Add…

You can give the path of existing flat file /create new flat file under Configuration.

For logging the informations into flat file ,select the options as shown in below image

Step 2: Execute the package and open the flatfile to see the logged informations.

Example 2: SSIS log provider for XML files

Step 1:

Select Provider type: SSIS log provider for XML files and click Add…

You can give the path of existing XML file /create new XML file under Configuration.

For logging the informations into XML file ,select the options as shown in below image

Step 2: Execute the package and open the XML file to see the logged informations.

 

Example 3: SSIS log provider for Windows Event Log

Step 1:

Select Provider type: SSIS log provider for Windows Event Log and click Add…

Step 2: Execute the package 

After package is executed, goto Control Panel - >  Administrative Tools - > Event Viewer - >Windows Logs - > click on Application - > under Source tab  with value “SQLISService” are package logged informations.

 

right click  on Control Flow tab and select Log Events,

you will notice as shown below.

Example 4: SSIS log provider for SQL Server

Step 1:

Select Provider type: SSIS log provider for SQL Server  and click Add…

 under Configuration create a connection to  SQL Server database.

For logging the informations into table in SQL Server database ,select the options as shown in below image

Step 2: Execute the package and then execute the below query in the database which you  have selected,while configuring logging options.

SELECT * FROM SYSSSISLOG

Example 5: SSIS log provider for SQL Server Profiler

Step 1:

Select Provider type: SSIS log provider for SQL Server Profiler and click Add…

You can create new *.trc  file under Configuration.

For logging the informations into *.trc  file ,select the options as shown in below image

Step 2: Execute the package and open the *.trc  file using SQL Server Profiler to see the logged informations as shown in below image

 

Connect to  SQL Server Profiler ,under File - > Open ->  goto the path of *.trc file ,which you  have given,while configuring logging options.