Como cambiar la seguridad de conexión en los paquetes DTS

by Maxi Accotto 12. marzo 2011 09:59

 

El siguiente código de VB.net y utilizando SQL-DMO permite conectarnos a un servidor SQL Server 2000 y cambiar las cadenas de conexión de los paquetes a seguridad integrada.

Recuerden antes de utilizar el código de hacer referencias a las clases de DTS del SQL-DMO

Code Snippet
  1. Public Class Form1
  2.     'Declare theobjects
  3.     Dim Opkg As DTS.Package = New DTS.Package()
  4.     Dim PkgName As String
  5.     Dim ServerName As String
  6.     Dim ConnectionString As String
  7.     Dim Con As Connections
  8.     Dim NumOfCon As Integer
  9.     Dim Str As String = ""
  10.     Dim myConnection As SqlConnection
  11.     Dim myCommand1 As SqlCommand
  12.     Dim myCommand2 As SqlCommand
  13.     Dim dr As SqlDataReader
  14.  
  15.     Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
  16.  
  17.     End Sub
  18.  
  19.     Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
  20.         'Establish ODBC connections with SQL Server Instance
  21.         myConnection = New SqlConnection("Server = servidor; uid = usuario; " &
  22.                                           "pwd = password; database =msdb")
  23.  
  24.             'Opening the connection
  25.             myConnection.Open()
  26.         'Executing the command and assigning it to my connection
  27.             myCommand1 = New SqlCommand("SELECT distinct(name) FROM sysdtspackages order by name", myConnection)
  28.         'Read from the datareader
  29.             dr = myCommand1.ExecuteReader()
  30.  
  31.             While dr.Read()
  32.             Try
  33.                 Opkg = New DTS.Package()
  34.                 'Load DTS package from SQL Server
  35.                 Opkg.LoadFromSQLServer("servidor", "usuario", "password",
  36.                                        DTS.DTSSQLServerStorageFlags.DTSSQLStgFlag_Default, "", "", "", dr(0).ToString())
  37.  
  38.                 ' Get the loaded DTS package name
  39.                 PkgName = Opkg.Name()
  40.                 'Get the number of connections within the loaded DTS package
  41.                 'Get the connection string of each connections of the DTS package
  42.  
  43.                 For NumOfCon = 1 To Opkg.Connections.Count
  44.                     Opkg.Connections.Item(NumOfCon).UseTrustedConnection = True
  45.                 Next
  46.  
  47.                 Opkg.SaveToSQLServer("servidor", "usuario", "password",
  48.                                      DTS.DTSSQLServerStorageFlags.DTSSQLStgFlag_Default, "", "", "", dr(0).ToString())
  49.  
  50.             Catch ex As Exception
  51.                 If Err.Number = -2147220454 Then
  52.                     Continue While
  53.                 Else
  54.                     MsgBox(Err.Description)
  55.                 End If
  56.  
  57.             End Try
  58.         End While
  59.  
  60.         proceso.Value = proceso.Value + 1
  61.         dr.Close()
  62.         MsgBox("Se termino el proceso")
  63.  
  64.     End Sub
  65. End Class

Tags:

DTS

Submit to DotNetKicks...
Permalink | Comentarios (0)

Agregar Comentario




biuquote
  • Comentario
  • Vista Previa
Loading


Maximiliano Damian Accotto