December 12, 2015 at 11:16 am
I have an SSIS package(attached screenshot) with for each loop which has a data flow task.
The data flow task has Ole db source and destination.
Before the for each loop I have a Execute SQL Task.
The execute SQL Task gathers the list of SQL Servers from a SQL table and populates it into a Foreach ADO Enumerator.
2 variables are declared
1st is system object type which has list of SQL servers(Serverlist)
2nd variable points to the current variable in the 1st variable.(Curent server)
My issue is the for each loop works fine as long as the SQL Server exists.But if the SQL Server does not exist or is not reacheable then the for lop fails.
How can make the for each loop still continue in case the servers are not reacheable or do not exist.
I have used Propogate=False in the evet handlers for the data flow task as executable and event handler has "OnError" option selected.
But my for each loop still fails.
My solution:
The for each loop should run inspite of the dataflow task failing(like unable to connect to a non existant server).
It should capture the server name which it was unable to connect to and push all the list of failed server connections to another sql table.
How can I achieve this.
As of now my solution works fine if the servers exist.
Thanks
December 14, 2015 at 1:10 pm
Change the MaximumErrorCount property on the foreach loop to something greater than 1. This way if there is an error the loop will continue and will only fail if it reaches the number of error you have specified.
Add some event handling into the onError event to record when an error has occurred.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply