September 27, 2009 at 9:29 pm
Hi,
I have a SSIS package that calls a .NET assembly from within a script task. The assembly is responsible for executing a bulk copy in 2 different situations:
1. If the target SQL table doesn't exist it creates it and then does bulk copy
2. If the target SQL table exists it will re-version that table by adding new fields and preserving any constraints, keys..etc that already exist, and then does bulk copy.
It identifies constraints by using either DMO or SMO to script indexes, but on one particular client site it bombs out with the error 'Server does not exist or access denied' for either method.
I have a test (WinForms) application that uses the same .NET code to script the indexes, and that works on the problem site without issue. It seems the calling of the assembly from SSIS is causing the problem.
When the assembly is called, I output diagnostics to record the current windows identity before the DMO/SMO methods are called. This user is SYSADMIN and should have sufficient access to the resources required (certainly does when run via the test app). Eg:
28/09/2009 2:53:36 p.m., ScriptIndex: Windows Identity: Name=TEST\simong User=S-1-5-21-1004336348-1715567821-725345543-5625 WillFlowAcrossNetwork=True Using SMO=True
Some sample code from the assembly:
'Connect to specified instance of SQL Server & database
Dim smoServer As Microsoft.SqlServer.Management.Smo.Server
smoServer = New Microsoft.SqlServer.Management.Smo.Server(mstrTargetServer)
If mblnRunDebug Then OutputLine(Date.Now.ToString() & ", ScriptIndex: SMO - connected to server")
Dim db As New Microsoft.SqlServer.Management.Smo.Database
db = smoServer.Databases(mstrTargetDatabase)
If mblnRunDebug Then OutputLine(Date.Now.ToString() & ", ScriptIndex: SMO - connected to database")
It throws the exception on either of these 2 lines:
Dim db As New Microsoft.SqlServer.Management.Smo.Database
db = smoServer.Databases(mstrTargetDatabase)
The problem presents when the package is started via DTExec or SQL Agent.
Any ideas?
Thanks
Simon
September 30, 2009 at 9:00 pm
Looks like a connection problem b\w Windows auth and SQL Auth. Your program may be running under windows auth and the user (maybe u) have access on every SQL Server instance except for the one in which u r getting the error. Where is the connection string for the SMO specified?
Try switching b\w
Windows Auth: Integrated Security = SSPI
SQL Auth: UserID = username; Password = password
October 1, 2009 at 6:19 pm
Have decided to use TSQL rather than SMO, DMO to scripts the constraints.
Thanks for your comment.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply