September 15, 2004 at 1:37 pm
I am trying to figure out a simple way to coordinate password changes on my oracle server with SQL Server DTS packages that write to the Oracle server. I have 50 or so packages, so I don't want to have to visit each dts package to update the password everytime it changes.
On the Oracle side I can use OS Authenticated ID's, but I can't seem to get it to work with DTS. Alternatively, if I could have some sort of script (ActiveX) that would take care of my Oracle connection, I could put the user ID and password in a SQL Server table once, and let the script make the connection as needed for each of the DTS packages. Although I don't know if DTS would like this from a Transformation Source standpoint.
Anybody have any experince with this or any ideas?
Thanks,
Shaun Funk
Database Consultant
Sara Lee Branded Apparel
September 16, 2004 at 9:31 am
The following script looks up the password from a table using ADO and then updates the connection. Simply add this as the first task of a DTS package and whenever your password changes, change it once in the table and all DTS packages will execute correctly without having to alter each package manually with the new password.
'**********************************************************************
' Visual Basic ActiveX Script
'************************************************************************
Function Main()
Dim objPkg
Dim objConnection
Dim myConn
Dim mySQL
Dim myRS
Dim myPwd
Set myConn = CreateObject("ADODB.Connection")
Set myRS = CreateObject("ADODB.Recordset")
myConn.ConnectionString = "Provider=SQLOLEDB;
Data Source=servername;
Initial Catalog=database_w_password;
User ID=username;
Trusted_Connection=Yes"
myConn.ConnectionTimeout = 0
myConn.Open
mySQL = "SELECT pwd_field FROM your_password_table"
myRS.Open mySQL, myConn
myRS.MoveFirst
myPwd = myRS(0)
myRS.Close
Set myRS = Nothing
' Get reference to the Connection
Set objPkg = DTSGlobalVariables.Parent
Set objConnection = objPkg.Connections("Other (ODBC Data Source)")
objConnection.Password = myPwd
Set objConnection = Nothing
Set objPkg = Nothing
Main = DTSTaskExecResult_Success
End Function
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply