May 12, 2009 at 4:43 pm
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.
Viewing 0 posts
You must be logged in to reply to this topic. Login to reply