I am having issues with the following code within a SSIS package. It is designed to drop all database users before a restore operation commences and following a kill all connections script. The issue I am running into is that at run-time this code generates an error saying the collection has changed after the first user is dropped which causes the script to exit. Anyone have any ideas what is wrong here? Of note, this is running against a SQL 2000 database not a SQL 2005 database.
Any help you can provide would be greatly appreciated.
' Microsoft SQL Server Integration Services Script Task
' Write scripts using Microsoft Visual Basic
' The ScriptMain class is the entry point of the Script Task.
' References Used, includes defaults:
Public Class ScriptMain
' The execution engine calls this method when the task executes.
' To access the object model, use the Dts object. Connections, variables, events,
' and logging features are available as static members of the Dts class.
' Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
' To open Code and Text Editor Help, press F1.
' To open Object Browser, press Ctrl+Alt+J.
Public Sub Main()
Dim srvr As Server = New Server(Dts.Variables("DestinationServer").Value.ToString)
Dim db As Database
db = srvr.Databases(Dts.Variables("DestinationDB").Value.ToString)
Dim usr As User
For Each usr In db.Users
' Check to see if the user is a system object, if not then drop the user
If Not usr.IsSystemObject Then
Dts.TaskResult = Dts.Results.Success
Catch ex As Exception
Dts.TaskResult = Dts.Results.Failure