The SQL Server Integration Services includes the Logging Services (Logs). So that you can use them in SSIS packages, containers, tasks etc. In this article we will show you the steps involved in SSIS logging (saving the log information). For this we are going to use the below shown data.
SSIS Logging Example
In order to explain the SSIS logging concept, Drag and drop the data flow task from the toolbox to control flow and rename it to SSIS LOGGING as we shown below.
Double click on it will open data flow tab. Drag and drop OLE DB Source, and OLE DB destination on to the data flow region.
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 OLE DB source editor.
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]. Please refer OLE DB Source in SSIS article to understand the available options in OLE DB Destination editor
Click on Mappings tab to check whether the source columns are exactly mapped to the destination columns.
Click OK to close the OLE DB Destination editor.
In order to configure logging, please navigate yourself to SSIS menu, and select the Logging (sub menu) as we shown below.
Once you select the Logging option, a new window called Configure SSIS Logs: SSIS Logging will be opened. As you can see from the below screenshot, it is showing a warning message
Please check mark the package (folder) to remove the warning message.
You can see the list of available logging options at the provider type property.
- Log Provider for SQL Server Profiler: Log information is saved in SQL Profiler
- Log Provider for XML Files: The logging information that you want to store will be written to XML file.
- Provider for SQL Server: Log information will be saved in the sysssislog table present in the SQL Server Database (System tables)
- Log Provider for Windows Event Logs: It will store the log information in the local computer. It is under the Application log on the Windows log.
- Log Provider for Text Files: Log information is saved in the comma separated value (CSV) format. The default extension is .log but you can change it to .txt as well.
Please select the log provider type, and click the Add button .First we will add the Log Provider for SQL server
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 SQL Tutorial Database.
Next, we added one more provider i.e., Text files. It means, log information will be stored in both SQL server, and the text file.
Currently we don’t have any File connection in the package so, click on the <New Connection..>.
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).
Next, go to the Details tab to configure the logs in-detail
Below picture will show you the list of available events in the SSIS Logging. Please read each and every event so that you can understand the meaning of them. As it is in simple English we are not explaining the events.
Let me select the OnInformation, OnPreExecute, OnPostExecte events
You can also click the Advanced button to further control the log information stored in the tables
Save button: This button is used to save the configuration settings that we configured here in XML file.
Load button: This button is used to load the existing configuration settings XML file.
Click OK to close the log settings, and run the package.
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.
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.
Thank You for Visiting Our Blog
Share your Feedback, or Code!!