Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

ErrorHandler problem Expand / Collapse
Author
Message
Posted Tuesday, November 13, 2012 9:02 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Saturday, April 5, 2014 9:02 AM
Points: 50, 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.
Post #1384140
Posted Tuesday, November 13, 2012 11:58 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 12:57 AM
Points: 13,640, Visits: 11,511
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?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1384409
Posted Wednesday, November 14, 2012 5:30 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Saturday, April 5, 2014 9:02 AM
Points: 50, Visits: 422
Hi Koen, both are False.

Regards,

Gordon.
Post #1384560
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse