Problem with Split() command in DTS

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)

Problem with Split() command in DTS

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2004-02-12 : 07:53:22

John writes "I have an ASP process that uses "Split()" - it works perfectly.
When I use the same code in a DTS Task, Split()'s arrays do not work.
Here is a sample record from my source data -
"AB04HC1250 216 216 216 0 9 000000"
Here is my code in ASP
-----------------------
if (asc(ch)=10) then
tSplit = Split(line, chr(9)) ....This works in DTS
tname = trim(tsplit(0)) ...This works in DTS
thigh = trim(tsplit(1)) ...This Fails in DTS but works in asp
tlow = trim(tsplit(2)) ...This Fails in DTS but works in asp
tclose = trim(tsplit(3))...This Fails in DTS but works in asp
tvolume = trim(tsplit(4))...This Fails in DTS but works in asp
toptin = trim(tsplit(5))...This Fails in DTS but works in asp
' The following splits the first array "tsplit(0)" as "tname"
sym = Split ( tSplit (0), 0 )...This works in DTS
sym1=trim ( sym (0) ) ...This works in DTS
yr = mid(tname,3,2)...This works in DTS
mo = mid(tname,5,1)...This works in DTS
----------------------------
When I remove references to the arrays "trim(tsplit (1)) - trim(tsplit (5))" in DTS, I get
"AB,04,H,C,1250,,,,,," by using the "sym, yr, mo sub code" above.

Can some clarify the proper use of Split()arrays in DTS???
(I'm using W2K/SP4 with SQL2K/SP4, and MSDC ver 2.8)
Thanks"

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2004-02-12 : 21:51:27

Can you show us some of your DTS (vb I presume) code ?

Damian

JohnB92882
Starting Member

10 Posts

Posted - 2004-02-14 : 09:11:07

MySourceCode=mid(MySourceCode,t) 'was t+1
l=len(mySourceCode)
t=1
dim fs, f, MyNewSourceCode
set fs=Server.CreateObject("Scripting.FileSystemObject")
set f=fs.CreateTextFile("C://Websites/DATA.txt",true)
MyNewSourceCode = ("SYM,YR,MO,OT,SP,HI,LO,CL,VO,OI,LTD,")
f.WriteLine(myNewSourceCode)
do
ch=mid(mySourceCode,t,1)
if (asc(ch)=10) then
tSplit = Split(line, chr(9))
tname=trim(tsplit(0))
thigh=trim(tsplit(1))
tlow=trim(tsplit(2))
tclose=trim(tsplit(3))
tvolume=trim(tsplit(4))
toptin=trim(tsplit(5))
tlasttradedate=trim(tsplit(6))
sym = split(tsplit(0),0)
sym1=trim(sym(0))
sym2=trim(sym(1))
y=len(tname)
z= len(sym1)
if z = 2 then
yr = mid(tname,3,2)
mo = mid(tname,5,1)
tp = mid(tname,6,1)
sp = mid(tname,7,6)
hi = thigh
lo = tlow
cl = tclose
vo = tvolume
oi = toptin
lt = tlasttradedate
MyNewSourceCode = (""&sym1&", "&yr&","&mo&","&tp&","&sp&","&hi&","&lo&","&cl&","&vo&","&oi&","<&"")
f.WriteLine(myNewSourceCode)
else
yr = mid(tname,2,2)
mo = mid(tname,4,1)
tp = mid(tname,5,1)
sp = mid(tname,6,6)
hi = thigh
lo = tlow
cl = tclose
vo = tvolume
oi = toptin
lt = tlasttradedate
'Response.Write ""&sym1&" "&yr& " "&mo& " "& tp& " "& sp& " "& hi& " "& lo& " "& cl& " "& vo& " "& oi& " "& lt & "<br>"
MyNewSourceCode = (""&sym1&","&yr&","&mo&","&tp&","&sp&","&hi&","&lo&","&cl&","&vo&","&oi&","<&"")
f.WriteLine(myNewSourceCode)
end if
line=""
else
line=line & ch
end if
t=t+1
loop until (t>=l)

'dim fs, f
'set fs=Server.CreateObject("Scripting.FileSystemObject")
'set f=fs.CreateTextFile("c:\test.txt",true)
'f.WriteLine(mySourceCode)
f.Close
set f=nothing
set fs=nothing

JohnB92882
Starting Member

10 Posts

Posted - 2004-02-14 : 09:18:17

Here's a few lines of the raw data - for the code above
----------------------------------------
BO04KC280 2410 2410 2410 2 2135 040120
BO04KC290 1930 1930 1930 50 171 040113
BO04KC295 1750 1400 1740 75 101 040121
BO04KC300 1550 1300 1550 27 3946 040121
BO04KC310 1500 1250 1250 50 1293 040121
BO04KC320 1050 1000 1050 5 241 040121
BO04KC340 680 550 680 50 1642 040121
BO04KC350 500 400 500 0 0 040121
BO04KC360 450 450 450 9 1961 040120
BO04KP185 5 5 5 0 20 000000
BO04KP190 5 5 5 1 44 031027
BO04KP200 5 5 5 2 20 031218
BO04KP205 5 5 5 200 263 040105
BO04KP210 5 5 5 48 721 040120
BO04KP215 10 10 10 40 309 040120
BO04KP220 20 20 20 2 3552 040120
BO04KP225 30 30 30 1 3 040112
BO04KP230 50 50 50 2 447 031222
BO04KP235 70 70 70 165 3898 040120
BO04KP240 120 100 100 107 547 040121
BO04KP245 150 150 150 9 307 040120

JohnB92882
Starting Member

10 Posts

Posted - 2004-02-14 : 09:23:14

Note to code -
set f=fs.CreateTextFile("C://Websites/DATA.txt",true)
'This writes the first line to the new text file
MyNewSourceCode = ("SYM,YR,MO,OT,SP,HI,LO,CL,VO,OI,LTD,")
f.WriteLine(myNewSourceCode)
''Here's where we start the split process
do

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2004-02-14 : 15:42:22

OK. In fact you say: the below does not work in MY package

t=split("a b c d e")
msgbox t(2)

But I hope it works and then your question should be revised.

JohnB92882
Starting Member

10 Posts

Posted - 2004-02-15 : 11:10:55

I don't understand your comment.
(However) My problem is that the code works perfectly in asp but delivers something else when its done from a DTS Task. My question is why does the split() command perform differently, and how can I correct the problem? VBScript in asp and VBScript in a DTS Task should act the same. The only diferences between both codes is that in asp I use the "Respnse.write" method, and in the DTS Task I use "writeline".
Thanks

JohnB92882
Starting Member

10 Posts

Posted - 2004-02-21 : 13:27:27

Update to problem ...
Well, I'd like to thank everyone for their input. I was unable to properly split the text line (as per above) so I resolved the problem with a work around. I combined the complete text line ("BO04KC280 2410 2410 2410 2 2135 040120") and the split line ( "AB,04,H,C,1250,,,,,," )into text 1 line ("AB,04,H,C,1250,,,,,,BO04KC280, 2410, 2410, 2410, 2, 2135, 040120" ), and then just ignored all columns I didn't need with the DTS transformation.
It's too bad that DTS ActiveX Tasks do not handle split arrays properly.
Thanks again for the assist

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources