Connecting to Oracle through TNS DSN, SqlSetConnectAttr failed

replace names accordingly
declare 3 global string variables in your DTS package and set the appropriate values for each one
my_DSN
my_DSN_USERID
my_DSN_PASSWORD

then in your activeX script:
dim sConnection
dim objConnection
sConnection      = "DSN=" & DTSGlobalVariables( "my_DSN" ).Value & _
      ";User Id=" & DTSGlobalVariables( "my_DSN_USERID" ).Value & _
      ";Password=" & DTSGlobalVariables( "my_DSN_PASSWORD" ).Value

set objConnection = CreateObject("ADODB.Connection")
' Open the connection to the databases
objConnection.ConnectionString = sConnection
objConnection.Open
etc etc

Thanks for the reply. I still ahve the same problem with the Connection string you gave me.

I tried the Following code first:

Function Main()
        Dim myConn
        Dim mySQlCmdText
        Dim myRecordset

     
        dim sConnection
        dim objConnection
       
        sConnection  = "Driver={Mcrosoft ODBC Driver for Oracle};Data source = 'odsqa.wireless' ; User id='disney_stg';Password='odsuser01'"

        set objConnection = CreateObject("ADODB.Connection")
        objConnection.ConnectionString = sConnection
        objConnection.Open
         
        Set myRecordset = CreateObject("ADODB.Recordset")
       
        Set mySQlCmdText = "SELECT * FROM STG.PROCESS_CONTROL WHERE DATE_FILE_UPLOADED = (SELECT MAX(DATE_FILE_UPLOADED) FROM STG.PROCESS_CONTROL)"

        myRecordset.Open mySQlCmdText, objConnection         ERROR THROWS AT THIS POINT  
End Function

Error: Object required.
[Microsoft][odbc driver]Driver's SQLSetConnectAttr failed

Even tried using the [Oracle in OraClient10g_home1] driver in the connection string, the same error again.

Second try :

Function Main()
        Dim myConn
        Dim mySQlCmdText
        Dim myRecordset

     
        dim sConnection
        dim objConnection

        sConnection  = "DSN = odsqa.wireless ; User id=disney_stg;Password=odsuser01"  <<ERROR:Data source name not found, changed it to data source and tried, get the previous error>>

        set objConnection = CreateObject("ADODB.Connection")
        objConnection.ConnectionString = sConnection
        objConnection.Open
         
        Set myRecordset = CreateObject("ADODB.Recordset")
 
        Set mySQlCmdText = "SELECT * FROM DISNEY_STG.PRECASH_PROCESS_CONTROL WHERE DATE_FILE_UPLOADED = (SELECT MAX(DATE_FILE_UPLOADED) FROM DISNEY_STG.PRECASH_PROCESS_CONTROL)"

        myRecordset.Open mySQlCmdText, objConnection
   
End Function


I tried catching the errors and clearing them, as in some forums they say that it is an informational message and can be neglected.
When i clear the errors i get the same error.

Error Properties are:
Native Error=0
Sql State =im006

Im running on SqlServer 2000/Windows Server 2000. Checked it on SQL Server 2000/Windows Server 2003, same error again.

Please help, ASAP. If i get my solution i will increase the points to 500.
                   

try removing the spaces, ie.
sConnection  = "DSN = odsqa.wireless ; User id=disney_stg;Password=odsuser01"  
becomes
sConnection  = "DSN=odsqa.wireless;User id=disney_stg;Password=odsuser01"

and obviously assuming the dsn names etc have been set up

the only other thing i can think of is to check your oracle tnsnames.ora file, that the correct parameters have been setup in that file and that it matches the Oracle connections that you set up in your odbc in control panel.

Ive tried that....The error message is Data source name not found. IF replace DSN with "Data Source", i get the object not found error during executing my SQL. The DSN has been setup and its working fine. Ive even tried the connecting using other DTS Connections like the Microsoft odbc to the same server and its fine. The TNSNAMES.ora file is fine. Ive registered a ticket with Microsoft and expecting a solution from them. Lets see............

Thank you, I appreciate your help. IF you can come up with anything else please suggest.