SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SMO within SSIS Script Task - User.Drop() causes error at runtime.


SMO within SSIS Script Task - User.Drop() causes error at runtime.

Author
Message
Scott Clark-275269
Scott Clark-275269
Old Hand
Old Hand (345 reputation)Old Hand (345 reputation)Old Hand (345 reputation)Old Hand (345 reputation)Old Hand (345 reputation)Old Hand (345 reputation)Old Hand (345 reputation)Old Hand (345 reputation)

Group: General Forum Members
Points: 345 Visits: 435
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


Todd Engen
Todd Engen
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2653 Visits: 6336
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


Scott Clark-275269
Scott Clark-275269
Old Hand
Old Hand (345 reputation)Old Hand (345 reputation)Old Hand (345 reputation)Old Hand (345 reputation)Old Hand (345 reputation)Old Hand (345 reputation)Old Hand (345 reputation)Old Hand (345 reputation)

Group: General Forum Members
Points: 345 Visits: 435
Thanks Todd! I will try this tomorrow when I put my dev hat back on Smile

Scott
Kenneth Fisher
Kenneth Fisher
SSCertifiable
SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)

Group: General Forum Members
Points: 6123 Visits: 2058
Any chance you know how to pull SQL Auth from a connection manager and use it with the server object?

Thanks
Kenneth

Kenneth FisherI 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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search