Migrating DTS packages to oracle

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

Migrating DTS packages to oracle

Author

Topic

nitin1353
Constraint Violating Yak Guru

381 Posts

Posted - 2005-07-12 : 13:54:28

Hi all
i have thse kind of DTS packages in SQL server which i will be migrating to oracle.Please help as these packages are written in VB script.

'**********************************************************************
' Visual Basic ActiveX Script
'************************************************************************

Function Main()
Dim rstLeads , rstsearch , cmdReport , strSQl , strFilePath ,cdoMsg , strBody , fso , rstCustomer ,flag,dt,out,substrsql, FstMortRate
dim ltype,dphone,ephone,OpenDB
dt="_" & Month(Date) & "_" & Day(Date) & "_" & Year(Date)
Set OpenDB = Createobject("ADODB.Connection")

Set rstCustomer = CreateObject("ADODB.Recordset")
Set rstLeads = CreateObject("ADODB.Recordset")
Set rstsearch =CreateObject("ADODB.Recordset")
OpenDB.Open "Driver={SQL Server};DATABASE= ABC;SERVER=(local);"
'OpenDB.Open "Driver={SQL Server};DATABASE=ABC;SERVER=(local);"
strSQl="SELECT tbl_Lead.Lead_ID, tbl_Lead.Submitted, tbl_Lead.First_Name, tbl_Lead.Last_Name, tbl_Lead.Address, tbl_Lead.City, "
strSQl=strSQl & " tbl_Lead.State,tbl_Lead.RemoteIP, tbl_Lead.ZIP, tbl_Lead.Day_Phone, tbl_Lead.Day_Phone_Ext, tbl_Lead.Evening_Phone, "
strSQl=strSQl & " tbl_Lead.Evening_Phone_Ext, tbl_Lead.Email, tbl_Lead.Best_Time_To_Call, tbl_Lead.Annual_Income, tbl_Lead.Monthly_Income, "
strSQl=strSQl & " tbl_Lead.source, "
strSQl=strSQl & " tbl_dbconleadDetails.*, tbl_Lead.Ques_Comments, tbl_Customer.Customer_id,tbl_Customer.Company, tbl_Customer.[Name], "
strSQl=strSQl & " tbl_CustLead.Status,tbl_CustLead.CL_ID FROM tbl_Lead JOIN tbl_dbconleaddetails ON tbl_Lead.Lead_ID =tbl_dbconleaddetails.lead_ID "
strSQl=strSQl & " LEFT JOIN tbl_CustLead ON tbl_Lead.lead_ID = tbl_CustLead.Lead_ID "
strSQl=strSQl & " LEFT JOIN tbl_Customer ON tbl_CustLead.Customer_ID=tbl_Customer.customer_ID "
strSQl=strSQl & " WHERE Submitted Between dbo.datepart(getdate()) AND dbo.datepart(getdate()+1) and tbl_Customer.customer_id in(2654,2760)"
strSQl=strSQl & " ORDER BY Submitted DESC "

rstLeads.Open strSQL, OpenDB,3,1

Do Until rstLeads.EOF
substrsql="select * from tbl_submitnationalfound where lead_id='" & trim(rstLeads("Lead_ID")) & "'"
rstsearch.Open substrsql,OpenDB,3,1
if rstsearch.EOF then

Dim myhttp,datatosend,sql
datatosend = "<?xml version=""1.0""?>" & vbnewline
datatosend = datatosend & "<PARAMS>" & vbnewline
if rstLeads("customer_id")=2654 then
datatosend = datatosend & "<auth username=""csi2"" password=""xml123"" campaignid=""1""/>"
elseif rstLeads("customer_id")=2760 then
datatosend = datatosend & "<auth username=""csi2"" password=""xml123"" campaignid=""6""/>"
end if

datatosend = datatosend & "<leads>" & vbnewline

datatosend = datatosend & "<lead HomePhone=""" & rstLeads("day_phone") & """ FirstName=""" & rstLeads("first_name") & """ LastName=""" & rstLeads("last_name") & """"
datatosend = datatosend & " Address1=""" & rstLeads("address") & """ Address2=""" & rstLeads("address") & """ City=""" & rstLeads("city") & """ State=""" & rstLeads("state") & """ Zip=""" & rstLeads("zip") & """"
datatosend = datatosend & " EmailAddress=""" & rstLeads("email") & """ BestContactTime=""" & rstLeads("Best_Time_To_Call") & """ TotalDebt=""" & rstleads("unsecuredDbAmt") & """"
datatosend = datatosend & " Fico=""500"" Derogs="""" IPAddress=""" & Ip_bittar & """ DateCreated=""" & date() & """ AltPhone=""" & rstLeads("evening_phone") & """"
datatosend = datatosend & " AltPhone2=""" & rstLeads("evening_phone") & """"
datatosend = datatosend & " VendersKey=""" & venkey & """ VendersSource=""" & sour & """ VendersRedirectURL=""4lowrates.com"" />"

datatosend = datatosend & "</leads>" & vbnewline
datatosend = datatosend & "</PARAMS>" & vbnewline

'-------------------------------------
'dim xml
'xml = datatosend

sql = "insert into tbl_submitnationalfound (lead_id,dateofsubmit,status,restext) values('" & trim(rstLeads("Lead_ID")) & "','" & now() & "','Fail','Failure')"
openDb.execute(sql)

Set myhttp=CreateObject("Msxml2.XMLHTTP")

myhttp.open "POST", "https://clients.nfdm.org/filestesting/default.asp", false
myhttp.setRequestHeader "lastCached", now()
myhttp.setRequestHeader "Content-Type", "text/xml"
myhttp.send datatosend

stat=myhttp.status
respo=myhttp.responseText

sql="update tbl_submitnationalfound set status='" & stat & "',restext='" & respo & "' where lead_id='" & trim(rstLeads("Lead_ID")) & "'"
openDb.execute(sql)

dim fss,filexml,strpathxml
set fss=CreateObject("scripting.FileSystemObject")
strpathxml="d:\federalequity_tempCSV\testJohnBittar.xml"
set filexml=fss.createtextfile(strpathxml)
filexml.writeline(myhttp.responsetext)
filexml.close
set myhttp = Nothing

rstLeads.MoveNext
else
rstLeads.MoveNext
end if
rstsearch.Close
Loop
rstLeads.close

set rstLeads=nothing
set rstsearch=nothing

OpenDB.close

Main = DTSTaskExecResult_Success
End Function

clarkbaker1964
Constraint Violating Yak Guru

428 Posts

Posted - 2005-07-12 : 16:43:49

XML generator... great!

You can do anything at www.zombo.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources