February 16, 2009 at 2:06 am
Hi,
I am using SSIS 2008.
I need to log the errors or the exceptions for my package. I have some questions which are as follows:
1. I tried using the sysssislog table removing those columns (like datacode, databytes) which I dont require? But the tables are not getting populated with the entries. It works fine if I include all the columns. Is there anything I need to change??
2. In the sysssislog table logging, I am logging OnError event only. I have a data flow task, in a sequence container which is in a For Each Loop container. Any error which gets log in the data flow task gets logged for sequence and For Each Loop container as well, thereby resulting in 3 rows instead of one record in the sysssislog table. Is there a way to avoid including these extra 2 records? I want to log only records for the container which is failed and not for the parent.
3. Is it a good way to log a package using event handlers in each task. (I have around 40 data flow tasks) i.e. logging a record for an error in the event handler task. What are the pros and cons of it over sysssislog table?
4. Is there any exception handling concept in SSIS for the data flow tasks etc? So, that the error can be avoided.
5. I am using a For Each Loop container, which is looping through a table, which is having the source connection strings. So the containers in the For Each Loop container will run for every new connection. So in this case, if any one of the server is not running in the middle of the loop, I don’t want the package to fail. For E.g.: I have 3 entries in the connection table, Server A,B and C. If the server is not running, then the loop should continue after B (after logging an error) to go to the next record. How do I achieve it?
Thanks in advance.
September 10, 2009 at 8:47 pm
Were you able to implement it? I need to implement the same. Please help.
Thanks in advance.
Manu
October 13, 2009 at 9:08 am
Stop the Task Within the For Loop Container from 'Propogating' the Error
December 28, 2009 at 11:03 am
Hi, can anyone explain how to achieve #5 in sqlbuddy's list? I have a similar scenario...
thanks!
Michael R.
December 29, 2009 at 3:18 am
(4): there is no specific exception handling for the data flow task. This is where you can insert the "event handler" logic for any particular event you may want to respond to. However, you will not be able to re-run the data flow using event handlers.
If you want to restart the package from the last point-of-failure, you can use package checkpoints. This way, next time the package will run, it will do so from the failed task and not from the beginning of the execution tree.
January 27, 2010 at 11:31 pm
For #5 set ForEachLoop container property ForceExecutionResult = Success so that loop will continue incase of failure
January 29, 2010 at 7:24 am
#1: have a look at the SP [dbo].[sp_ssis_addlogentry]
#2: this is by design; you'll find a workaround here - personally I don't mind about the multiple entries and I never tried this one
#3: I use both (event handler and sysssislog). Because of the phenomenon you describe in #2, it is sufficient to create just one event handler on the package level (I guess this will no longer be true if you implement the workaround from #2)
#4: I'm not sure what error you're referring to but try this. On some destination components (e.g. OLE DB but not RAW) there is a red arrow. Attach a row count component to it and link it to an integer variable. Now your data flow task will not fail when something goes wrong with the insert. In the control flow you can still find out by checking the value of the row count.
#5: Raise the MaximumErrorCount property of the Foreach Loop Container to a value bigger than 1 (see my answer to a similar question here).
May 24, 2011 at 9:13 am
Thank you for that. It was driving me crazy!
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply