SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


ErrorHandler problem


ErrorHandler problem

Author
Message
YaHozna
YaHozna
SSC-Enthusiastic
SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)

Group: General Forum Members
Points: 130 Visits: 422
I'm hoping that someone can point me in the right direction with my error handling problem which is now seriojusly driving me nuts.

I'm building a Data Warehouse of Safety Data for my company which involves creating an SSIS package that will, initially, connect to a number of SQL Servers using a ForEach Loop and dynamic connection. The ForEach Loop contains a single Data Flow (to query the current SQL Server from the dynamic connection) which, in turn, contains a single OLE DB Source and OLE DB Destination to write basic info about the current SQL Server to a 'staging' database.

The problem I'm facing is that, even after creating an Error Handler for the Data Flow and setting its 'Propagate' property to False, errors in the OLE DB Source caused by not being able to connect to the current SQL Server are propagating back up to the parent ForEach Loop and, after what seems like an arbitray 5 failures, causes the whole package to fail. I don't actually need, or indeed want, to connect to each and every SQL Server as I just write a 'no connection' flag to the 'staging' database in the event of a connection failure.

I can see the errors are being handled by adding a Script component to the Error Handler to generate a message on each error but there doesn't seem to be a way to get round the problem except by setting the MaximumErrorCount on the ForEachLoop to some arbitrarily high number. Does the OLE DB Source have some perculiar property that causes the parent ForEach Loop component to fail when an error occurs in the Data flow or am I missing or misunderstanding something here?

Hope someone can enlighten me.

Regards,

Gordon.
Koen Verbeeck
Koen Verbeeck
SSC-Dedicated
SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)

Group: General Forum Members
Points: 35371 Visits: 13270
What are the property values for FailParentOnFailure and FailPackageOnFailure?


How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
YaHozna
YaHozna
SSC-Enthusiastic
SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)

Group: General Forum Members
Points: 130 Visits: 422
Hi Koen, both are False.

Regards,

Gordon.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search