?
Solved

SSIS Add Error Logging

Posted on 2008-06-24
10
Medium Priority
?
784 Views
Last Modified: 2013-11-30
SQL Server Integration Services

I use a data flow item containing multiple lookups to normalize a flat file input. The error output I save into a seperate table. This table contains the errors from all lookups. I would like to add an additional error code to identify which lookup caused the error.

How can this be done?
0
Comment
Question by:riffrack
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
  • 2
10 Comments
 
LVL 60

Expert Comment

by:chapmandew
ID: 21854718
Are you referring to which particular lookup task caused the error, or what row from the lookup caused the error?
0
 
LVL 8

Expert Comment

by:drydenhogg
ID: 21854981
Bit of a hack but throw a column transform in there and add an additional column to the error output from the lookup specifying which lookup it was that failed.
0
 

Author Comment

by:riffrack
ID: 21856713
hi drydenhogg
I tried to do that but I receive the following error:
"The component does not allow adding columns to this input or output."

hi chapmandew
yes, I am referring to which particular lookup task caused the error
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 8

Expert Comment

by:drydenhogg
ID: 21856731
Do it prior to each lookup, so it appears as a column before you hit the error output.
0
 

Author Comment

by:riffrack
ID: 21856888
Sorry, but its not quite clear to me what you mean.

I am quite new to SSIS, can you explain it step by step?
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 21856940
OK, so each task has a succes and failure workflow right (green and red arrows).  For the failure of each task (red arrow), use an ExecuteSQL statement to log the task name , and the error.  Does that make sense?
0
 
LVL 8

Accepted Solution

by:
drydenhogg earned 2000 total points
ID: 21856971
In the data flow, before the raw data goes through a lookup, put the derived column transformation within the flow to add a column specifying which lookup you are about to do, if the lookup fails, the output will include the data you added in the lookup.

You probably have a data source object with a green arrow going directly to a lookup object. Basically divert the green arrow to the dervied colum transform, and then from the transform back to the lookup.

Before each subsequent lookup you can alter the value to indicate which lookup you are about to do.

Within the derived column object you can specify a column name, under the derived column you can say '<add as new column>' and in the expression put the value such as 'CustomerID Lookup' or whatever is appropriate for your application.

When a row then errors on a specific lookup, the lookup which it failed on will be in that derived column, which will of been sent to the error output.
0
 
LVL 8

Expert Comment

by:drydenhogg
ID: 21856987
This is still a bit of a hack though, it's not pretty and will have a performance penalty.
0
 

Author Closing Comment

by:riffrack
ID: 31470066
Thanks a lot works fine, I was able to place the derived column after the lookup. That was exactly what I was looking for.
0
 

Author Comment

by:riffrack
ID: 21860036
Performance is not really an issue, as it will run once a month with less than 100'000 records.

Is there a recommended / elegant solution, which you would refer to as a hack?
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Suggested Courses

741 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question