Microsoft – SQL Server – Integration Services (SSIS) – Business Intelligence Studio – Dealing with data integrity errors – Using Package Logging

Introduction

Wanted to see how useful SSIS Package logging is when run-time data inconsistency errors occur.

 

Enable Logging at SSIS Package Level

 

  1. Launch Business Intelligence Studio (BIDS)
  2. Load SSIS Project
  3. Access SSIS Package
  4. Access SSIS Design pane
  5. Access the “Control Flow” tab
  6. In the Control-Flow tab, right click in an empty spot, and from the drop-down menu, select the “Logging” menu item
  7. In the “Configure SSIS Logs”, make the changes detailed below
    1. In the Containers’s tab, enable logging at the package level
    2. In the Providers and Logs tab, add “SSIS log provider for Text files” as a provider type
    3. In the Details tab, enable “OnError” / “OnTaskFailed” /”OnWarning”

 

Before

ConfigureSSISLogs-Before

 

 

After – Providers and Logs

 

ConfigureSSISLogs-After-ProviderAndLogs

 

 

After – Providers and Logs

 

ConfigureSSISLogs-After-Details

 

 

Log File Contents

Here is what we captured in the log-file:



OnError,PF00N9ME20B7,DBLAB\dadeniji,Data Flow Task,{675AA3C7-8057-41FB-8F05-6929845F2746},{7B5D3817-390E-4B87-BAE6-3C1DD41D00D3},8/4/2014 10:10:44 AM,8/4/2014 10:10:44 AM,-1071636471,0x,SSIS Error Code DTS_E_OLEDBERROR.  
An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 11.0"  Hresult: 0x80004005  Description: "Unspecified error".

OnError,PF00N9ME20B7,DBLAB\dadeniji,xferUsingLogging,{A51961CB-EB18-48C8-8A38-67EE876A7F9A},{7B5D3817-390E-4B87-BAE6-3C1DD41D00D3},8/4/2014 10:10:44 AM,8/4/2014 10:10:44 AM,-1071636471,0x,SSIS Error Code DTS_E_OLEDBERROR.  
An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 11.0"  Hresult: 0x80004005  Description: "Unspecified error".

OnError,PF00N9ME20B7,DBLAB\dadeniji,Data Flow Task,{675AA3C7-8057-41FB-8F05-6929845F2746},{7B5D3817-390E-4B87-BAE6-3C1DD41D00D3},8/4/2014 10:10:44 AM,8/4/2014 10:10:44 AM,-1071607780,0x,
There was an error with 
OLE DB Destination.Inputs[OLE DB Destination Input].Columns[inceptionDate]
on OLE DB Destination.Inputs[OLE DB Destination Input]. 
The column status returned was: 
"The value violated the integrity constraints for the column.".

OnError,PF00N9ME20B7,DBLAB\dadeniji,xferUsingLogging,{A51961CB-EB18-48C8-8A38-67EE876A7F9A},{7B5D3817-390E-4B87-BAE6-3C1DD41D00D3},8/4/2014 10:10:44 AM,8/4/2014 10:10:44 AM,-1071607780,0x,
There was an error with 
OLE DB Destination.Inputs[OLE DB Destination Input].Columns[inceptionDate] 
on OLE DB Destination.Inputs[OLE DB Destination Input]. 
The column status returned was: 
"The value violated the integrity constraints for the column.".



 

 

 

SQL Server Profiler

 

Let us see how useful SQL Server Profiler is when we encounter incomplete data errors.

 

Events Selection

 

Here is the “Events Selection”

 

Events Selection

SQLServerProfiler-TraceProperties

 

 

 Tabulated

Events Event Detail Column
Errors and Warnings
ErrorLog Error
EventLog Error
Exception Error
User Error Message Error
Stored Procedures
 RPC:Completed  Error
TSQL
SQL:BatchStart
SQL:BatchCompleted  Error

 

 

 

 

Events

Here are the captured events :

 

SQLServerProfiler

 

Interpretation

There are a couple of points we noticed when we executed the SSIS package and reviewed the generated “SQL Server Profiler” session:

  • There are no occurrences of “Errors and Warnings”.  This means that our ETL tool internally and gracefully handles the errors
  • The SQL:BatchCompleted event exposes a value 1 in the Error Column

 

 

 

Summary

In summary, SSIS Logging can be useful to capture run time errors.

But, unfortunately the errors captured is not very precise.

Leave a Reply

Please log in using one of these methods to post your comment:

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