'********************************************************************** ' Visual Basic ActiveX Script '************************************************************************ Function Main() sDSN = "driver={SQL Server};server=MARSHNET-DATA;uid=lgoutdoors;pwd=tahr2004;database=LGoutdoors" set cn = createobject("ADODB.Connection") cn.open sDSN set rs = cn.execute ("SELECT TOP 1 * FROM kFiles ORDER BY rcvdate DESC, [size] DESC") 'msgbox rs("filename") if isnull (rs("filename")) or rs("filename") = "" then Main = DTSTaskExecResult_Success exit function else fname = rs("filename") Set xmlDoc = CreateObject("MSXML2.DOMDocument") xmlDoc.async = False xmlDoc.setProperty "ServerHTTPRequest", True xmlDoc.Load("D:\Ebay\FTP Log\Kinseys\Drop\inventory\" &rs("filename")) ' msgbox "D:\Ebay\FTP Log\Kinseys\Drop\" &rs("filename") if (xmlDoc.parseError.errorCode <> 0) then ' msgbox "Error in line " & xmlDoc.parseError.line &"
" &_ ' " position " & xmlDoc.parseError.linePos &"
" &_ ' "Error Code: " & xmlDoc.parseError.errorCode &"
" &_ ' "Error Reason: " & xmlDoc.parseError.reason &"
" &_ ' "Error Line: " & xmlDoc.parseError.srcText exit function end if Set nodeList = XMLDoc.SelectNodes("Item") Set nodeList = xmlDoc.getElementsByTagName("Item") For Each item In nodeList strSQL = "" set node=item.getElementsByTagName("ItemNo") ItemNo = node.item(0).text set node=item.getElementsByTagName("QtyAvailable") QtyAvailable = node.item(0).text set node=item.getElementsByTagName("Price") Price = node.item(0).text set node=item.getElementsByTagName("Status") Status = node.item(0).text set node=item.getElementsByTagName("Description") Description = node.item(0).text set node=item.getElementsByTagName("UPC") UPC = node.item(0).text set node=item.getElementsByTagName("MapPrice") MapPrice = node.item(0).text set node=item.getElementsByTagName("Weight") Weight = node.item(0).text Description = replace(Description, "'", "") Description = replace (Description, chr(34), "in") Description = replace (Description, ",", "") strSQL = "Insert into KinseyProducts (ItemNo, QtyAvailable, Price, Status, Description, UPC, MapPrice, Weight) " & _ " values('', " &QtyAvailable &", " &Price &", '" &Status &"', '', '" &UPC &"', " &MapPrice &", " &Weight &")" 'response.write strSQL &"
" cn.execute ("delete from sqlerr") bflag = 0 if UPC = "697216200562" then bflag=1 end if if bflag=1 then msgbox strSQL bflag = 0 end if sSQL = replace(strSQL, "'", "") cn.execute ("insert into sqlerr ([sql]) values ('" &sSQL &"')") cn.execute (strSQL) next 'response.write "Operation Complete." Set xmlDoc = Nothing Set nodeList = Nothing Set node = Nothing end if set cn = Nothing Main = DTSTaskExecResult_Success End Function