Home > Integration Services > Logging in SSIS… using SQL Server Log Provider

Logging in SSIS… using SQL Server Log Provider


As per MS BOL SSIS provides a diverse set of log providers, and gives you the ability to create custom log providers. The Integration Services log providers can write log entries to text files, SQL Server Profiler, SQL Server, Windows Event Log, or XML files. This help in implementing logging in SSIS packages. With logging you can capture run-time information about a package, helping you audit and troubleshoot a package every time it is run.

Following are the different log providers:
1. Text File
2. SQL Server Profiler
3. SQL Server
4. Windows Event
5. XML File

Here we will see how can we use the SQL Server log provider (#3 above), which writes log entries to the sysssislog table in a SQL Server database.
Please note in 2005 the logs are stored in sysdtslog90 table. In 2008 the table name is changed to sysssislog.

Following are the easy steps one can follow to configure logging:

1. Create a SSIS package, as shown below are the 2 Execute SQL tasks. To enable logging goto menu, select SSIS-> Logging…

2. On this new window select the Provider Type as “SSIS log provider for SQL Server”, click Add.

3. The log provider will be added, select by checking the check box. Now provide the connection where the log table should get created.

4. Switch to the Details tab and select the events you want to log and audit. Click OK, save your SSIS package and your logging is enabled.

5. After this run your package and switch to  SSMS and check on object explorer that the table sysdtslog90 has been created on the database you added as a connection. Now you can query the sysdtslog90 table where the logs are stored.

select * from FROM [AdventureWorks].[dbo].[sysdtslog90]

The sysdtslog90 table contains following columns listed in the query below. All are self explanatory, one can also check the links at the end of the post to get more info about them.

SELECT [id], [event], [computer], [operator], 1, --,[sourceid], [executionid],
      [starttime],[endtime],[datacode], [databytes], [message]
FROM [AdventureWorks].[dbo].[sysdtslog90]

Links on SSIS Log Providers & Implementing them:
http://msdn.microsoft.com/en-us/library/ms140246.aspx
http://msdn.microsoft.com/en-us/library/ms138020.aspx

sysdtslog90

About these ads
  1. January 16, 2013 at 11:22 am

    Good one :)

  2. Wilson
    March 18, 2013 at 8:34 pm

    Why would I get duplicates for each row? 6 x what I need…

    message
    “component “Flat File Destination” (16)” wrote 609 rows.
    “component “Flat File Destination” (16)” wrote 609 rows.
    “component “Flat File Destination” (16)” wrote 609 rows.
    “component “Flat File Destination” (16)” wrote 609 rows.
    “component “Flat File Destination” (16)” wrote 609 rows.
    “component “Flat File Destination” (16)” wrote 609 rows.

  1. July 5, 2011 at 3:51 am

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 405 other followers

%d bloggers like this: