changing the connection password hrough another package

• sas-473105

  SSC Enthusiast

  Points: 109

  More actions

  May 12, 2009 at 4:43 pm

  #130087

  I am trying to change the connection password in an existing package through another package,

  i am not changing any other properties of the connection like Servername, Userid etc. this done thru the below script code.

  'sample package name and location

  Dim mySample As String = "C:\SSIS\SampleSSISPackage.dtsx"

  ' Create an application and load the sample.

  Dim app As Application = New Application()

  Dim pkg As Package = app.LoadPackage(mySample, Nothing)

  Dim myConns As Connections = pkg.Connections

  Dim myCMgr As ConnectionManager

  Dim oCon As ConnectionManager

  Dim oProp As DtsProperty

  For Each myCMgr In myConns

  'this is an ADO.Net connection manager

  If myCMgr.Name = "DB Connection1" Then

  ''Change the Password

  oProp = myCMgr.Properties("Password")

  oProp.SetValue(myCMgr, "ABC123")

  'this is an OLEDB connection manager

  ElseIf myCMgr.Name = "DB Connection2" Then

  ''Change the Password

  oProp = myCMgr.Properties("Password")

  oProp.SetValue(myCMgr, "123ABC")

  End if

  Next

  pkg.ProtectionLevel = DTSProtectionLevel.EncryptSensitiveWithPassword

  pkg.PackagePassword = "pass1"

  app.SaveToXml(mySample, pkg, Nothing)

  "DB Connection1" is connecting to IBM DB2 database using the DSN, and it uses the Provider '.Net Providers\Odbc Data Provider' - this is used by 'DataReader Source'

  "DB Connection2" is connecting to SQL Server DB, and it uses the provider 'Native OLE DB\SQL Native Client' - this connection is used in 'OLE DB Destination'

  the ProtectionLevel property of the existing packages are set to 'EncryptSensitiveWithPassword'

  After executing the above script, the connection passwords are not stored in the package.

  if i run the sampleSSISpackage.dtsx, it gives 'PASSWORD MISSING' error for connection1 and 'Login failed' for connection2.

  how do we change the connection password thru the code.

  Any help on this highly appreciated.

  sasi.