DTS中导出txt文件

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

Function EligFlag(xDate) if isnull(xDate) or  trim(xDate & " ")="" then   EligFlag="N"  else EligFlag="Y" end if End Function

Function MakeString(fld, typ, length) select case typ  case "Sdate"   if isnull(fld) or cstr(fld) = "12/31/2020" then    MakeString = "000000"   else    fld = trim(cstr(fld))    MakeString = replace(left(fld,10),"/","")   end if   case "date"             if fld="" then    MakeString = "00000000"   else    if Cdate(fld) = #12/31/2020# then     MakeString = "99991231"    else            MakeString =  Year(CDate(fld)) & String(2 - Len(Month(fld)), "0") & Month(CDate(fld)) & String(2 - Len(Day(fld)), "0")  & Day(CDate(fld))             MakeString = Trim(CStr(MakeString))            MakeString = Replace(MakeString, "/", "")    end if   end if   case "integer"      fld = trim(cstr(fld))   fldlength = len(fld)   if fldLength < length then    for i = 0 to length - (fldlength + 1)     MakeString = makestring & "0"    next     makestring = makestring & cstr(fld)   else    MakeString = left(fld,length)   end if  case "string"   if isnull(fld) or fld = "" then    MakeString = space(length)   else    fld = trim(cstr(fld))    fldLength = length - len(fld)    if right(fld,1) = "," then      fldLength = fldLength + 1     fld = left(fld, len(fld) - 1)    end if    spaces = ""    for i = 1 to fldLength     spaces = spaces & " "    next    MakeString =  ucase(left(fld,length) & spaces)   end if  case "money"   if isnull(fld) or fld = "0.00" then    MakeString = space(length - 1) & "0"   else    fld = trim(cstr(FormatCurrency(fld)))    fldlength = len(fld)    if fldLength < length then     MakeString = space(length - fldLength) & fld    else     MakeString = left(fld,length)    end if   end if  case "Time"   if isnull(fld) or fld = "" then    MakeString = space(length)   else    makeString = left(replace(fld,":",""),6)   end if end selectend function

Function Main() set conn = createObject("adodb.connection") conn.Open "driver={sql server};server=bert; uid=internetguest;pwd=guest;database=membercalls" 'set rs = conn.execute("resultsempdep") set rs = conn.execute("sp_slcresultempdep_mon_temp") dim fsoif len(Month(date))=2 thenmymonth = Month(date) elsemymonth = "0" & Month(date) end if

if len(Day(date))=2 thenmyday = Day(date) elsemyday = "0" &  Day(date) end if

set fso = createobject("scripting.FileSystemObject")myfilename = "D:/ACHMB" & mymonth & myday & ".txt"'myfilename = "//bert/VOL1/RXVender/NewPrx/ACHMB" & mymonth & myday & "A.txt"set file = fso.createTextFile(myfilename)

 numOfRecords = 0

 do while not rs.eof and not rs.bof  if left(rs("memberid"), 5) <> "11111"  then

  clientid = makestring(rs("clientid"),"string",15)   groupid = makestring(rs("groupid"),"integer",6) & "000      "                memberid = makestring("M"&rs("groupid")&rs("empnumber"),"string",18)                personcode = makestring(rs("personcode"),"integer",3)                sex = rs("sex") if rs("relationshipcode")=0 then                relationshipcode = makestring(1,"integer",1) else      if rs("relationshipcode")=1 then  relationshipcode = makestring(2,"integer",1)  else  relationshipcode = makestring(3,"integer",1)  end if  end if             '   relationshipcode = makestring(rs("relationshipcode"),"integer",1)

                effectivedate = makestring(rs("effectivedate"),"date",8)   terminationdate = makestring(rs("terminationdate"),"date",8)  memberlastname =   makestring(rs("memberlastname"),"string",15)  memberfirstname =   makestring(rs("memberfirstname"),"string",12)  addressline1 =   makestring(rs("addressline1"),"string",30)  addressline2 =   makestring(rs("addressline2"),"string",25)  city =   makestring(rs("city"),"string",20)  st =   makestring(rs("state"),"string",2)  zipcode =    makestring(rs("zipcode"),"string",5) &"     "  birthday =   makestring(rs("birthdate"),"date",8)  memberphonenumber =   "          "  anniversarydate = makestring(rs("anniversarydate"),"date",8)  miscellaneousgrouping1 = makestring("","string",10)  miscellaneousid = makestring("","string",12)  miscellaneousdata = makestring(rs("memberid"),"integer",9)&makestring("","string",21)  pponame = makestring(rs("pponame"),"string",30)  coveragetype = "  "  filler = makestring("","string",100)

      file.write clientid   file.write groupid   file.write memberid   file.write personcode   file.write sex   file.write relationshipcode   file.write effectivedate   file.write terminationdate   file.write memberlastname   file.write memberfirstname   file.write addressline1   file.write addressline2   file.write city   file.write st   file.write zipcode   file.write birthday   file.write memberphonenumber   file.write anniversarydate   file.write miscellaneousgrouping1   file.write miscellaneousid   file.write miscellaneousdata   file.write pponame   file.write coveragetype   file.write filler & vbcrlf  end if    if rs.eof then exit do  rs.movenext loop

 rs.close  set conn = nothing

 file.close set file = nothing set fso = nothing Main = DTSTaskExecResult_Success

End Function