April 14, 2008 at 2:34 am
I am using the SMO Enumerator to loop through databases. However the package fails when it gets to a DB that is offline. How can I exclude offline databases from my ForEach Loop?
April 15, 2008 at 11:22 am
You can set properties for the task that connects to each db -- FailParentOnFailure and FailPackageOnFailure are "False" and MaximumErrorCount = 9999. With these settings, even if the task fails, the loop will continue. Of course, subsequent tasks should be done only with the default "success" criterion in their precedence constraints (green arrows).
April 16, 2008 at 8:24 am
I have tried setting the Max Number of Errors on the Dataflow task which sits inside the For Each loop but the package still fails.
Is there anyway to filter which database that the SMO ForEach loops through?
April 17, 2008 at 7:32 am
Populate a recordset with a query against sys.databases.
[font="Courier New"]SELECT [name]
FROM SYS.DATABASES
WHERE state_desc = 'ONLINE'[/font]
Then use a for each ADO enumerator to loop through the recordset.
Kindest Regards,
Frank Bazan
April 18, 2008 at 3:01 am
Frank Bazan (4/17/2008)
Populate a recordset with a query against sys.databases.[font="Courier New"]SELECT [name]
FROM SYS.DATABASES
WHERE state_desc = 'ONLINE'[/font]
Then use a for each ADO enumerator to loop through the recordset.
Thanks, that was the solution I came up with myself. I then passed the databasename into the connection string.
It just seems a stupid way of doing it seeing as there is an SMO enumerator that should do things like this.
April 18, 2008 at 3:44 am
You're right. It doesn't seem to be a particularly good way to derive your InitialCatalog for the connection manager.
There's one other thing I'd add... whilst it's possible to set the maximum errors for your component, it isn't the best way to deal with an 'acceptable' failure. If you ever need to progress your package after a known error, you should add an event handler and set the propagate variable to false where the known error condition is true. So in the event handler you could add an ExecuteSQL task to return a true or false value to find out if the db is online and use this to set the propagate variable. By doing this you're positively asserting which errors are acceptable and which ones aren't. Other wise your package might be failing for another reason one day (in this case possibly a login expired, or incorrect permissions) and the package will still continue.
HTH
Kindest Regards,
Frank Bazan
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply