Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SMO within SSIS Script Task - User.Drop() causes error at runtime. Expand / Collapse
Author
Message
Posted Tuesday, February 19, 2008 10:10 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 8:28 AM
Points: 134, Visits: 287
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.

Scott

' 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:
' Microsoft.SqlServer.ConnectionInfo
' Microsoft.SqlServer.ManagedDTS
' Microsoft.SqlServer.ScriptTask
' Microsoft.SqlServer.Smo
' Microsoft.Vsa
' System
' System.Data
' System.Windows.Forms
'

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports Microsoft.SqlServer.Management.Smo

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()
Try
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
usr.Drop()
End If
Next

Dts.TaskResult = Dts.Results.Success

Catch ex As Exception
Dts.TaskResult = Dts.Results.Failure

End Try
End Sub

End Class

Post #457502
Posted Tuesday, February 19, 2008 11:37 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, November 12, 2013 8:44 AM
Points: 1,073, Visits: 6,334
Replacing the For Each loop with a For Next loop should work.

Dim usr as User

For x As Integer = db.Users.Count - 1 to 0 Step -1
usr = db.Users(x)
If Not usr.IsSystemObject Then
usr.Drop()
End If
Next x

Post #457554
Posted Tuesday, February 19, 2008 4:17 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 8:28 AM
Points: 134, Visits: 287
Thanks Todd! I will try this tomorrow when I put my dev hat back on :)

Scott
Post #457713
Posted Monday, February 7, 2011 9:23 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Thursday, September 11, 2014 4:39 PM
Points: 3,462, Visits: 1,800
Any chance you know how to pull SQL Auth from a connection manager and use it with the server object?

Thanks
Kenneth


Kenneth Fisher
I strive to live in a world where a chicken can cross the road without being questioned about its motives.
--------------------------------------------------------------------------------
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Link to my Blog Post --> www.SQLStudies.com
Post #1059659
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse