SMO Enumerator

Question

SMO Enumerator

planetmatt

SSCrazy

Points: 2207
More actions
April 14, 2008 at 2:34 am

#121396

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?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

john.arnott SSChampion Points: 11882 More actions · Answer 1

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).

planetmatt SSCrazy Points: 2207 More actions · Answer 2

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?

Frank Bazan SSCertifiable Points: 5378 More actions · Answer 3

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

planetmatt SSCrazy Points: 2207 More actions · Answer 4

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.

Frank Bazan SSCertifiable Points: 5378 More actions · Answer 5

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