Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

changing the connection password hrough another package Expand / Collapse
Posted Tuesday, May 12, 2009 4:43 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, July 13, 2012 5:26 AM
Points: 3, Visits: 59
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


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.
Post #715535
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse