'********************************************************************** ' Last Updated 01-13-03 '********************************************************************** Function Main() '==Set Globals==================================== TheOutputServer = DTSGlobalVariables("gblOutputServer").Value TheOutputDatabase = DTSGlobalVariables("gblOutputDatabase").Value TheOracleAccount = DTSGlobalVariables("gblOracleAccount").Value TheOracleServer = DTSGlobalVariables("gblOracleServer").Value ' TheOutputTableName = "IRR_AH90_CUSTEFF_CSV" ' '===Input queries================================== TheOracleQuery1 = "SELECT 'FOS2000' AS DSOURCE, DECODE(RTRIM(LTRIM(PROGRAM)), " & _ "'37X','73X', PROGRAM) AS MODEL, 'CUSTOMER' AS EFFTYPE, " & _ "PROGRAM || '-' || DERIVATIVE || MINOR_MODEL_ALPHA AS LCONFIG, " & _ "MIN(VARIABLE_NO) AS FROMEFF, VAR_BLOCK_END AS THRUEFF, " & _ "NVL(RTRIM(LTRIM(CUSTOMER_CODE)), ' ') AS CUS " & _ "FROM FOS2000.VW_AUTOSURPLUS_CFO " & _ "GROUP BY PROGRAM, CUSTOMER_CODE, VAR_BLOCK_END, DERIVATIVE, MINOR_MODEL_ALPHA " & _ "ORDER BY PROGRAM, VAR_BLOCK_END" ' TheOracleQuery = TheOracleQuery1 ' '===Connection to SQLEVT66======================== TheMMOMetConnStr = "DRIVER={SQL Server};" & _ "Regional=Yes;" & _ "Trusted_Connection=Yes;" & _ "SERVER=" & TheOutputServer & ";" & _ "DATABASE=" & TheOutputDatabase '===Open ADO Connection========================== Set TheMMOMetConn = CreateObject("ADODB.Connection") With TheMMOMetConn .CommandTimeout = 10000 .ConnectionTimeout = 10000 .Open TheMMOMetConnStr End With '===Clear Old Records============================== Set TheCommandObject = CreateObject("ADODB.Command") TheCommandObject.ActiveConnection = TheMMOMetConn TheCommandObject.CommandText = "Truncate Table " & TheOutputTableName TheCommandObject.CommandType = 1 TheCommandObject.Execute Set TheCommandObject = Nothing '===Open table for writing============================ Set TheOutputTableRS = CreateObject("ADODB.Recordset") With TheOutputTableRS .ActiveConnection = TheMMOMetConn .LockType = 3 .Source = TheOutputTableName .CursorType = 3 .Open End With '===Open Oracle connection========================== Set TheOraSession = CreateObject("OracleInProcServer.XOraSession") Set TheOraDatabase = TheOraSession.OpenDatabase(TheOracleServer, TheOracleAccount, vbNull) Set TheEmpDynaset = TheOraDatabase.CreateDynaset(TheOracleQuery, vbNull) '===Transfer the data================================ With TheOutputTableRS While Not TheEmpDynaset.EOF .AddNew For Each AnOracleField In TheEmpDynaset.Fields On Error Resume Next .Fields(AnOracleField.Index).Value = TheEmpDynaset.Fields(AnOracleField.Index).Value If Err.Number <> 0 Then .Fields(AnOracleField.Index).Value = "NONE" End If On Error Goto 0 Next .Update TheEmpDynaset.MoveNext Wend End With '===Cleanup======================================= TheEmpDynaset.Close Set TheEmpDynaset = Nothing Set TheOraSession = Nothing TheOutputTableRS.Close Set TheOutputTableRS = Nothing TheMMOMetConn.Close Set TheMMOMetConn = Nothing '====Outtahere===================================== Main = DTSTaskExecResult_Success End Function