• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1996
  • Last Modified:

How to log errors in SSIS?

I need to log errors that outputs the row, column and error description for auditing.
For each field in the source file that violates the validation rules.

Can you please help me out in this? I am using SSIS for the first time.

Thanks.
0
dorsun
Asked:
dorsun
  • 5
  • 5
1 Solution
 
PedroCGDCommented:
I suggest you
1. to buy a book. (In my blog there is a link to a free SQL 2008 book)
2. To use redirect rows (red output) of each component
3. Use SSIS Log Provider (Follow images)

helped?
Regards,
Pedro
www.pedrocgd.blogspot.com
SSIS-LoggingProvider1.JPG
SSIS-LoggingProvider2.JPG
SSIS-LoggingProvider3.JPG
SSIS-LoggingProvider4.JPG
SSIS-LoggingProvider5.JPG
0
 
dorsunAuthor Commented:
Pedro,

I was following your suggestion to log errors. But I dont see and table / SP created in the database like 'sysdtslog90'.

Please can yo ugive more details...

Thanks.
0
 
PedroCGDCommented:
follow the images I attached, execute one time the package and you will see the objects created... you must execute it once!
regards,
Pedro
0
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
dorsunAuthor Commented:
Hi Pedro,

Thanks it worked.
But is there any way that I can store meaningful data like the field, row id, message etc.. into the log?

Thanks.
0
 
PedroCGDCommented:
field? row id? customized message?

For these, you need to do some custom work... and more hard if you want row by row... but take a look to the error output of each component that allows you to redirect the rows into a error destination.

I have a post in my blog abou this, take a look
www.pedrocgd.blogspot.com
0
 
dorsunAuthor Commented:
Pedro,

You have a wonderful blog!!

Can yo uplease help me get the specific information that I need?
I am using SSIS for the first time and have strict deadlines to complete.
Please help me in this error processing.

Thanks alot!
0
 
PedroCGDCommented:
0
 
dorsunAuthor Commented:
Pedro,

Can you please help me to log customized errors?
I need the field name, meaningful error desc, unique key of the row and one configured value.
I need this on an urgent basis.
I know you have a solution for this.

Thanks!
0
 
PedroCGDCommented:
Check this example:
http://blogs.conchango.com/jamiethomson/archive/2005/06/11/SSIS_3A00_-Custom-Logging-Using-Event-Handlers.aspx#8755

Follow the preview images to use the automatic log provider of SSIS.
Helped?
regards,
Pedro
0
 
dorsunAuthor Commented:
Thank you
0

Featured Post

Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

  • 5
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now