SMO Server object + Connection Manager in Script task

• Kenneth.Fisher

  SSC-Insane

  Points: 20396

  More actions

  February 4, 2011 at 12:31 pm

  #244712

  I'm trying the following script

  Dim sourceConn As ServerConnection

  sourceConn = DirectCast(Dts.Connections("SOURCE").AcquireConnection(Dts.Transaction), _

  ServerConnection)

  Dim src As New Server(sourceConn)

  My connection manager is using a SQL Login with a saved password. It is an ADO.NET connection. The SERVER() requires a ServerConnection to work. I could create it with just the name of the server but then I don't have the user name or password.

  I'm getting an error on the DirectCast telling me that it can't convert my connection to a ServerConnection. I can convert it to a SqlConnectionObject but can't get from there to the ServerConection. Of course the ServerConnection object has a SqlConnectionObject property that would go the other way but that doesn't help me much.

  Thanks for any help

  Kenneth

  Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]