Making SSIS package to accept dynamic oledb connetions

SSIS oledb connection manager does't allow user to connect to different oledb servers. If we store oledb connection sting in Config file then it will accepts server name, InitialCatalog and username. But it wont accept password. The is because oledb connection manager has not exposed the oledb password property. To over come this problem we have to override the OLEDB connection manager itself. So that it accepts connections to different oledb servers.

Public Sub Main()
Dim vars As Variables

Dim oledbConnectionManager As ConnectionManager
oledbConnectionManager = Dts.Connections("<oledb connection name>")

' ADDED TO MAKE oledb CONNECTION STRING DYNAMIC
Dim oServerName As String
Dim oUserName As String
Dim oInitialCatalog As String
Dim oPassword As String

Dts.VariableDispenser.LockOneForWrite("oServerName", vars)
oServerName = vars("oServerName").Value.ToString().Trim()
vars.Unlock()

Dts.VariableDispenser.LockOneForWrite("oInitialCatalog", vars)
oInitialCatalog = vars("oInitialCatalog").Value.ToString().Trim())
vars.Unlock()

Dts.VariableDispenser.LockOneForWrite("oUserName", vars)
oUserName = vars("oUserName").Value.ToString().Trim())
vars.Unlock()

Dts.VariableDispenser.LockOneForWrite("oPassword", vars)
oPassword = vars("oPassword").Value.ToString().Trim()) '
vars.Unlock()

Dts.Connections("<oledb connection name>").Properties("ServerName").SetValue(oledbConnectionManager, oServerName)
Dts.Connections("<oledb connection name>").Properties("InitialCatalog").SetValue(oledbConnectionManager, oInitialCatalog)
Dts.Connections("<oledb connection name>").Properties("UserName").SetValue(oledbConnectionManager, oUserName)
Dts.Connections("<oledb connection name>").Properties("Password").SetValue(oledbConnectionManager, oPassword )

Dts.TaskResult = Dts.Results.Success

End Sub