Please place comments above....

My error:
Unknown token received from SQL server
Protocol error in TDS stream

What is this and how/if can/it be corrected????

Details...
DTS Pkg w/a connection to a Linked Excel spreadsheet with a range web

In QA: select * from openquery(WebImport,'select * from web')
Returns all rows, no errors noted. (I haven't tried an insert-update to my
destination table, that's next)

FYI, when I used a direct connection to Excel, I was only getting one row at a
time, so I switched to a linkedserver and open query.

TIA

JeffP....

Anyway my long-as* DTS ActiveXScript...... parsed OK of course...

Function Main()
'--------

DTSDestination("RECORDSTATUS") = 0

'--------
if mid(DTSSource("FORM_TYPE"),1,4) = "COMP" Then
DTSDestination("COMPANY") = "Comprehensive Life Insurance"
elseif mid(DTSSource("FORM_TYPE"),1,4) = "APIT" Then
DTSDestination("COMPANY") = "American Physicans Insurance Trust"
end if
'--------
Dim strCorr

If IsNull(DTSSource("CORRESPONDENCE")) or _
RTrim(DTSSource("CORRESPONDENCE")) = "NULL" or _
Len(DTSSource("CORRESPONDENCE")) < 1 Then
strCorr = "R"
End If
'--------
If strCorr <> "R" Then
strCorr = UCase(Mid(DTSSource("CORRESPONDENCE"),1,1))
End If
'--------
DTSDestination("UCORRESPTO") = strCorr


If DTSSource("OFFICE_ADDRESS") <> NULL or _
DTSSource("OFFICE_ADDRESS") <> "NULL" and _
DTSSource("OFFICE_ADDRESS") > "" Then

DTSDestination("UWORKADDR1") = RTrim(left(DTSSource("OFFICE_ADDRESS"),40))
DTSDestination("UWORKADDR2") = _
RTrim(Mid(DTSSource("OFFICE_ADDRESS"),40,Len(DTSSo urce("OFFICE_ADDRESS"))))
End If
'--------
If DTSSource("OFFICE_CITY") <> NULL or _
DTSSource("OFFICE_CITY") <> "NULL" and _
DTSSource("OFFICE_CITY") > "" Then

DTSDestination("UWORKCITY") = Left(DTSSource("OFFICE_CITY"),30)
End If
'--------
If DTSSource("OFFICE_STATE") <> NULL or _
DTSSource("OFFICE_STATE") <> "NULL" and _
DTSSource("OFFICE_STATE") > "" Then

DTSDestination("UWORKSTATE") = UCase(Left(DTSSource("OFFICE_STATE"),2))
End If
'--------
If DTSSource("OFFICE_ZIP") <> NULL or _
DTSSource("OFFICE_ZIP") <> "NULL" and _
DTSSource("OFFICE_ZIP") > "" Then

DTSDestination("UWORKZIP") = UCase(Left(DTSSource("OFFICE_ZIP"),5))
End If
'--------
DTSDestination("CONTACT_SP") = DTSSource("SPOUSE_FIRST_NAME")
DTSDestination("LASTNAME_SP") = DTSSource("SPOUSE_LAST_NAME")

Dim strFirstName
Dim strLastName

If DTSSource("SPOUSE_LAST_NAME") <> NULL or _
DTSSource("SPOUSE_LAST_NAME") <> "NULL" and _
DTSSource("SPOUSE_LAST_NAME") > "" Then

strLastName = UCase(mid(DTSSource("SPOUSE_LAST_NAME"),1,1)) & _

LCase(mid(DTSSource("SPOUSE_LAST_NAME"),2,Len(DTSS ource("SPOUSE_LAST_NAME"))))
End If
'--------
If IsNull(strLastName) or Len(strLastName) < 1 Then
strLastName = UCase(mid(DTSSource("LAST_NAME"),1,1)) & _
LCase(mid(DTSSource("LAST_NAME"),2,Len(DTSSource(" LAST_NAME"))))

End If
'--------
If InStr(strLastName," ") > 0 Then
strLastName = UCase(left(strLastName,1)) &
LCase(mid(strLastName,2,InStr(strLastName," ")-2)) & _
UCase(mid(strLastName,InStr(strLastName," "),2)) &
LCase(mid(strLastName,InStr(strLastName," ")+2,Len(strLastName)))
End If
'--------
If DTSSource("SPOUSE_FIRST_NAME") <> NULL or _
DTSSource("SPOUSE_FIRST_NAME") <> "NULL" and _
DTSSource("SPOUSE_FIRST_NAME") > "" Then

strFirstName = UCase(mid(DTSSource("SPOUSE_FIRST_NAME"),1,1)) & _

LCase(mid(DTSSource("SPOUSE_FIRST_NAME"),2,Len(DTS Source("SPOUSE_FIRST_NAME"))))

DTSDestination("LASTNAME_SP") = strLastName
'--------
If InStr(strFirstName," ") > 0 Then
strFirstName = UCase(left(strFirstName,1)) &
LCase(mid(strFirstName,2,InStr(strFirstName," ")-2)) & _
UCase(mid(strFirstName,InStr(strFirstName," "),2)) &
LCase(mid(strFirstName,InStr(strFirstName," ")+2,Len(strFirstName)))
End If
End If
'--------
If Len(strFirstName) >= 1 Then
DTSDestination("CONTACT_SP") = RTrim(strFirstName & " " & strLastName)
End If
'--------

strFirstName = ""
strLastName = ""

strLastName = ucase(mid(DTSSource("LAST_NAME"),1,1)) & _
lcase(mid(DTSSource("LAST_NAME"),2,len(DTSSource(" LAST_NAME"))))

If InStr(strLastName," ") > 0 Then
strLastName = UCase(left(strLastName,1)) &
LCase(mid(strLastName,2,InStr(strLastName," ")-2)) & _
UCase(mid(strLastName,InStr(strLastName," "),2)) &
LCase(mid(strLastName,InStr(strLastName," ")+2,Len(strLastName)))
End If
'--------
DTSDestination("LASTNAME") = strLastName

strFirstName = ucase(mid(DTSSource("FIRST_NAME"),1,1)) & _
lcase(mid(DTSSource("FIRST_NAME"),2,len(DTSSource( "FIRST_NAME"))))

If InStr(strFirstName," ") > 0 Then
strFirstName = UCase(left(strFirstName,1)) &
LCase(mid(strFirstName,2,InStr(strFirstName," ")-2)) & _
UCase(mid(strFirstName,InStr(strFirstName," "),2)) &
LCase(mid(strFirstName,InStr(strFirstName," ")+2,Len(strFirstName)))
End If
'--------

DTSDestination("CONTACT") = strFirstName & " " & strLastName

dim strBMonthDay
dim strBYear

If DTSSource("APPLICANT_DOB_MONTH") <> NULL or _
DTSSource("APPLICANT_DOB_MONTH") <> "NULL" and _
DTSSource("APPLICANT_DOB_MONTH") > "" Then

strBMonthDay = RTrim(DTSSource("APPLICANT_DOB_MONTH"))

If DTSSource("APPLICANT_DOB_DAY") <> NULL or _
DTSSource("APPLICANT_DOB_DAY") <> "NULL" and _
DTSSource("APPLICANT_DOB_DAY") > "" Then
strBMonthDay = strBMonthDay & "/" & RTrim(DTSSource("APPLICANT_DOB_DAY"))
End If

If len(RTrim(DTSSource("APPLICANT_DOB_YEAR"))) < 4 Then

strBYear = "/" & "19" & Right(RTrim(DTSSource("APPLICANT_DOB_YEAR")),2)

ElseIf len(DTSSource("APPLICANT_DOB_YEAR")) = 4 Then

strBYear = "/" & RTrim(DTSSource("APPLICANT_DOB_YEAR"))
End If

DTSDestination("UBIRTHDATE") = strBMonthDay & strBYear

End If
'--------

strBMonthDay = ""
strBYear = ""

If DTSSource("SPOUSE_DOB_MONTH") <> NULL or _
DTSSource("SPOUSE_DOB_MONTH") <> "NULL" and _
DTSSource("SPOUSE_DOB_MONTH") > "" Then

strBMonthDay = RTrim(DTSSource("SPOUSE_DOB_MONTH"))

If DTSSource("SPOUSE_DOB_DAY") <> NULL or _
DTSSource("SPOUSE_DOB_DAY") <> "NULL" and _
DTSSource("SPOUSE_DOB_DAY") > "" Then
strBMonthDay = strBMonthDay & "/" & RTrim(DTSSource("SPOUSE_DOB_DAY"))
End If

If len(RTrim(DTSSource("SPOUSE_DOB_YEAR"))) < 4 Then

strBYear = "/" & "19" & Right(RTrim(DTSSource("SPOUSE_DOB_YEAR")),2)

ElseIf len(DTSSource("SPOUSE_DOB_YEAR")) = 4 Then

strBYear = "/" & RTrim(DTSSource("SPOUSE_DOB_YEAR"))
End If

DTSDestination("UBIRTHDATE_SP") = strBMonthDay & strBYear

End If


if DTSSource("SPOUSE_GENDER") <> NULL or _
DTSSource("SPOUSE_GENDER") <> "NULL" and _
DTSSource("SPOUSE_GENDER") > "" Then
DTSDestination("USEX_SP") = left(UCase(DTSSource("SPOUSE_GENDER")),1)

End If
'--------

Dim strFT
Dim strIN

If DTSSource("APPLICANT_HEIGHT_FT") <> NULL or _
DTSSource("APPLICANT_HEIGHT_FT") <> "NULL" and _
DTSSource("APPLICANT_HEIGHT_FT") > "" Then

strFT = RTrim(DTSSource("APPLICANT_HEIGHT_FT"))

End If
'--------

If DTSSource("APPLICANT_HEIGHT_IN") <> NULL or _
DTSSource("APPLICANT_HEIGHT_IN") <> "NULL" and _
DTSSource("APPLICANT_HEIGHT_IN") > "" Then

strIN = RTrim(DTSSource("APPLICANT_HEIGHT_IN"))
End If
'--------

If strFT > "" and strIN > "" Then

DTSDestination("UHEIGHT") = strFT & "/" & strIN

ElseIf strFT > "" and strIN < "" Then

DTSDestination("UHEIGHT") = strFT & "/0"
End If
'--------

strFT = ""
strIN = ""

If DTSSource("SPOUSE_HEIGHT_FT") <> NULL or _
DTSSource("SPOUSE_HEIGHT_FT") <> "NULL" and _
DTSSource("SPOUSE_HEIGHT_FT") > "" Then

strFT = RTrim(DTSSource("SPOUSE_HEIGHT_FT"))

End If
'--------

If DTSSource("SPOUSE_HEIGHT_IN") <> NULL or _
DTSSource("SPOUSE_HEIGHT_IN") <> "NULL" and _
DTSSource("SPOUSE_HEIGHT_IN") > "" Then

strIN = RTrim(DTSSource("SPOUSE_HEIGHT_IN"))
End If
'--------

If strFT > "" and strIN > "" Then

DTSDestination("UHEIGHT") = strFT & "/" & strIN

ElseIf strFT > "" and strIN < "" Then

DTSDestination("UHEIGHT") = strFT & "/0"

End If
'--------

If DTSSource("APPLICANT_WEIGHT") <> NULL or _
DTSSource("APPLICANT_WEIGHT") <> "NULL" and _
IsNumeric(DTSSource("APPLICANT_WEIGHT")) Then

DTSDestination("UWEIGHT") = DTSSource("APPLICANT_WEIGHT")
End If
'--------

If DTSSource("SPOUSE_WEIGHT") <> NULL or _
DTSSource("SPOUSE_WEIGHT") <> "NULL" and _
IsNumeric(DTSSource("SPOUSE_WEIGHT")) Then

DTSDestination("UWEIGHT_SP") = DTSSource("SPOUSE_WEIGHT")
End If
'--------


If DTSSource("APPLICANT_NICOTINE") <> NULL or _
DTSSource("APPLICANT_NICOTINE") <> "NULL" and _
DTSSource("APPLICANT_NICOTINE") > "" Then

DTSDestination("USMOKER") = Left(UCase(DTSSource("APPLICANT_NICOTINE")),1)
End If
'--------

If DTSSource("SPOUSE_NICOTINE") <> NULL or _
DTSSource("SPOUSE_NICOTINE") <> "NULL" and _
DTSSource("SPOUSE_NICOTINE") > "" Then

DTSDestination("USMOKER_SP") = Left(UCase(DTSSource("SPOUSE_NICOTINE")),1)
End If
'--------

If DTSSource("APPLICANT_PILOT") <> NULL or _
DTSSource("APPLICANT_PILOT") <> "NULL" and _
DTSSource("APPLICANT_PILOT") > "" Then

DTSDestination("UPILOT") = Left(UCase(DTSSource("APPLICANT_PILOT")),1)
End If
'--------

If DTSSource("SPOUSE_PILOT") <> NULL or _
DTSSource("SPOUSE_PILOT") <> "NULL" and _
DTSSource("SPOUSE_PILOT") > "" Then

DTSDestination("UPILOT_SP") = Left(UCase(DTSSource("SPOUSE_PILOT")),1)
End If
'--------

DTSDestination("UPERS_HIST") = UCase(Left(DTSSource("APPLICANT_HISTORY"),1))

DTSDestination("UPERS_HIST_SP") = UCase(Left(DTSSource("SPOUSE_HISTORY"),1))

If IsNumeric(DTSSource("INSURANCE_AMOUNT")) Then
DTSDestination("UPOLAMOUNT") = DTSSource("INSURANCE_AMOUNT")
End If
'--------

If IsNumeric(DTSSource("SPOUSE_INSURANCE_AMOUNT")) Then
DTSDestination("UPOLAMOUNT_SP") = DTSSource("SPOUSE_INSURANCE_AMOUNT")
End If
'--------

If IsNumeric(DTSSource("PREMIUM_YEARS")) Then
DTSDestination("UPOLYEARS") = DTSSource("PREMIUM_YEARS")
End If
'--------

If IsNumeric(DTSSource("SPOUSE_PREMIUM_YEARS")) Then
DTSDestination("UPOLYEARS_SP") = DTSSource("SPOUSE_PREMIUM_YEARS")
End If
'--------

Main = DTSTransformStat_OK
End Function

Have a look at these possible reasons

http://tinyurl.com/2r3z6

--
--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.allisonmitchell.com - Expert SQL Server Consultancy.
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org


"JDP@Work" <JPGMTNoSpam (AT) sbcglobal (DOT) net> wrote

))
))))