Logging in SSIS

Friends,

We all know how important LOGGING is for any data process projects. In this post we will see how the logging can be done in SSIS packages. The main purpose of logging is that it provides the complete information about the events occurred during the execution of the process. Capturing such information will be helpful in the following things –

• Helps in identifying the errors.
• Helps in identifying the bottlenecks for performance issues.
• Helps in doing RCAs.

SSIS contains built-in logging features that capture execution details about your packages. Logging enables you to record information about events you are interested in as the package runs. The logging information of a package can be stored in the following locations –

• Text file
• XML file
• Sql Server table
• Windows event log
• Sql Profiler

Logging can be enabled for all or some tasks and containers and for all or any events. Tasks and containers can inherit the settings from parent containers. Multiple logs can be set up, and a task or event can log to any or all logs configured. You also have the ability to control which pieces of information are recorded for any event.

Let’s see the steps involved in implementing logging for a package in SSIS.

• Create a package with any couple of control flow tasks.
• Go to SSIS menu on top and select LOGGING option which opens a wizard to configure logging as shown below.

• To enable logging, you must first check the box next to the package name in the left pane. Notice that the checkboxes for the child objects in the package are grayed out. This means that they will inherit the logging properties of the package. You can click into any checkbox to uncheck an object. Clicking again to check the box will allow you to set up logging properties specific for that task or container.

• Next, in the provider drop down, select any provider type of your wish and in this example I will go with “SSIS log provider for TEXT Files” (an easiest one to explain .. :)) and click on ADD.
• Here you can see a log is added and you can edit the name,description if you wish. Check the check box on the left and click on CONFIGURATION to set the new connection as shown below.

• Click the drop-down under Configuration and choose <New Connection>. Once the File Connection Manager Editor opens, set the Usage Type property to Create File. Type c:\SSIS_Log.txt as the path for the TEXT file or click Browse to the TEXT file location as shown below.

• If you wish to select an EXISTING FILE then you have to select option “Existing File” for USAGE TYPE property in the above pic.
• Click the Details tab to view a list of events that you can log. By clicking Advanced, you will also see a list of possible fields.
• Here you can select all the events for which you wish to log the information. In this example I enabled logging for ONLY 3 e vents as shown below.

• Click on OK and run the package. Once the package execution has completed, open the log file to view the logs. You can see the logs in the below given format.

This is it .. In this way you can log the information of the events occurring in ssis package. As discussed, we can log the information to many destinations. Try it !!

Regards,
Roopesh Babu V