SSIS Logging

The SQL Server Integration Services includes the Logging Services (Logs). So, you can use them in SSIS packages, containers, tasks, etc. Let me show, what are the steps involved in SSIS logging (saving the log information). For this SSIS logging demonstration, we are going to use the below-shown data.

SSIS Logging 0

SSIS Logging Example

To explain the SSIS logging concept, first, drag and drop the data flow task from the toolbox to control flow and rename it to SSIS LOGGING, as we have shown below.

SSIS Logging 1

Double click on it will open the SSIS data flow tab. Drag and drop OLE DB Source, and OLE-DB destination on to the data flow region.

SSIS Logging 2

Double click on OLE-DB source will open the Source Editor. From the below screenshot, you can see that we are using the [Employee] table present in the SQL Tutorial database. Please refer OLE DB Source in SSIS article to understand the available options in the OLEDB source editor.

SSIS Logging 3

Double-click on the OLE DB Destination will open the to OLE DB Destination Editor to configure the target database. For the time being, we are selecting the existing table Table called [SSIS Logging Example Table]. 

SSIS Logging 4

Click on the Mappings tab to check whether the source columns accurately mapped to the destination columns.

SSIS Logging 5

Click OK to close the OLE DB Destination editor.

Configure Logging in SSIS

To configure SSIS logging, please navigate yourself to the SSIS menu and select the Logging (submenu), as we shown below.

SSIS Logging 6

Once you select the SSIS Logging option, a new window called Configure SSIS Logs: SSIS Logging will open. As you can see from the below screenshot, it is showing a warning message

SSIS Logging 7

Please checkmark the package (folder) to remove the warning message.

SSIS Logging 8

You can see the list of available SSIS logging options at the provider type property.

  • Log Provider for SQL Server Profiler: This saves the Log information in SQL Profiler
  • Log Provider for XML Files: The logging information will store to an XML file.
  • Provider for SQL Server: It saves the Log information in the sysssislog table present in the SQL Database (System tables)
  • Log Provider for Windows Event Logs: It will store the SSIS log information in the local computer. It is under the Application log on the Windows log.
  • Log Provider for Text Files: This option saves the Log information in the comma-separated value (CSV) format. The default extension is .log, but you can change it to .txt as well.
SSIS Logging 9

SQL Server Logging Configuration

Please select the log provider type, and click the Add button. First, we will add the Log Provider for SQL server

SSIS Logging 10

Next, we have to configure the connection string (Database along with the credentials). For now, we are selecting the existing connection that is pointing to the SQL Tutorial database.

SSIS Logging 11

Text File Logging in SSIS Configuration

Next, we added one more provider, i.e., Text files. It means log information will store in both the SQL server and the text file.

SSIS Logging 12

Currently, we don’t have any File connection in the package so, click on the <New Connection..>.

SSIS Logging 13

Once you click on the <New Connection..> option, File Connection Manager Editor will be opened to configure it. In this example, we are creating a new file. So, we are selecting Create File option from the Usage Type.

Next, Click on the Browse button, and add the file name at the location (file system).

SSIS Logging 14

Next, go to the Details tab to configure the logs in-detail

SSIS Logging 15

Configure Logs in Logging

The below picture will show you the list of available events in the SSIS Logging. Please read each event so that you can understand the meaning of them. As it is in simple English, we are not explaining the log events.

For the demo purpose, we selected the OnInformation, OnPreExecute, OnPostExecte events

SSIS Logging 16

You can also click the Advanced button to control further the log information stored in the tables

SSIS Logging 17

Save button: This button is to save the configuration settings of an SSIS logging that we configured here in an XML file.

SSIS Logging 18

Load button: Use this button to load the existing log configuration settings XML file.

SSIS Logging 19

Click OK to close the SSIS log settings, and run the designed package.

SSIS Logging 20

Let me open the SSIS Logging file present in the file system. From the below screenshot, you can see the log information in the specified text file.

SSIS Logging 21

Please open the SQL Server Management Studio. Under the specified database (here it is SQL Tutorial), and go to the Tables folder -> System Tables. Here you will find the sysssislog table.

SSIS Logging 22

Comments are closed.