November 13, 2012 at 9:02 am
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.
November 13, 2012 at 11:58 pm
What are the property values for FailParentOnFailure and FailPackageOnFailure?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
November 14, 2012 at 5:30 am
Hi Koen, both are False.
Regards,
Gordon.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply