DTS Transformation problem- Banging my head against the wall!!

[Mrbaseball34]

I'm trying to import the lines from a text file shown below
into a table using DTS with a transformation script.
(
fields in the file are not separated by '|', it is just
for illustration
)

           1             2          3          4         5            6         7    
1234|56|7890|123|456|789|012345|6789012345|6789012345|6789012345|67|89012345|67890123
2002|01|0021|001|997|   |006720|0000154560|0000268800|0000268800|  |        |20020601  
2002|01|0021|001|   |   |006720|0000154560|0000268800|0000268800|  |        |20020601  

{Source may wrap}

Here is the script:

Function Main()
Dim strVar
Dim strDec
	DTSDestination("ReinYear") = DTSSource("Col001")
	DTSDestination("StateCode") = DTSSource("Col002")
	DTSDestination("CropCode") = DTSSource("Col003")
	if ((Len(Trim(DTSSource(&quot;Col004&quot;))) > 0) and (Trim(DTSSource(&quot;Col004&quot;)) <> &quot;000&quot;)) then
		DTSDestination(&quot;CountyCode&quot;) = DTSSource(&quot;Col004&quot;)
	else
		DTSDestination(&quot;CountyCode&quot;) = NULL
	end if
	if (Len(Trim(DTSSource(&quot;Col005&quot;))) > 0)  and (Trim(DTSSource(&quot;Col005&quot;)) <> &quot;000&quot;)then
		DTSDestination(&quot;TypeCode&quot;) = DTSSource(&quot;Col005&quot;)
	else
		DTSDestination(&quot;TypeCode&quot;) = NULL
	end if
	if (Len(Trim(DTSSource(&quot;Col006&quot;))) > 0)  and (Trim(DTSSource(&quot;Col006&quot;)) <> &quot;000&quot;)then
		DTSDestination(&quot;PracticeCode&quot;) = DTSSource(&quot;Col006&quot;)
	else
		DTSDestination(&quot;PracticeCode&quot;) = NULL
	end if
	strVar = Left(DTSSource(&quot;Col007&quot;), 5)
	strDec = Right(DTSSource(&quot;Col007&quot;), 1)
	DTSDestination(&quot;HighYield&quot;) = CStr(Replace(FormatNumber(strVar & &quot;.&quot; & strDec, 1), &quot;,&quot;, &quot;&quot;, 1, -1, 1))
	strVar = Left(DTSSource(&quot;Col008&quot;), 8)
	if (CLng(strVar) = 99999999) then
		DTSDestination(&quot;WarnYield&quot;) = NULL
	else
		strDec = Right(DTSSource(&quot;Col008&quot;), 2)
  		DTSDestination(&quot;WarnYield&quot;) = CStr(Replace(FormatNumber(strVar & &quot;.&quot; & strDec, 2), &quot;,&quot;, &quot;&quot;, 1, -1, 1))
	end if
	strVar = Left(DTSSource(&quot;Col009&quot;), 8)
	strDec = Right(DTSSource(&quot;Col009&quot;), 2)
	DTSDestination(&quot;MaxYield&quot;) = CStr(Replace(FormatNumber(strVar & &quot;.&quot; & strDec, 2), &quot;,&quot;, &quot;&quot;, 1, -1, 1))
	strVar = Left(DTSSource(&quot;Col010&quot;), 8)
	strDec = Right(DTSSource(&quot;Col010&quot;), 2)
	DTSDestination(&quot;OverrideYield&quot;) = CStr(Replace(FormatNumber(strVar & &quot;.&quot; & strDec, 2), &quot;,&quot;, &quot;&quot;, 1, -1, 1))

	DTSDestination(&quot;OverrideFlag&quot;) = DTSSource(&quot;Col011&quot;)
	DTSDestination(&quot;UserId&quot;) = DTSSource(&quot;Col012&quot;)
	DTSDestination(&quot;Moddate&quot;) = DTSSource(&quot;Col013&quot;)
	Main = DTSTransformStat_OK
End Function

Null replacement is working just fine, however the Yield
data keeps coming out as:

HighYield     : 672.0    (correct)
WarnYield     : 1545.00  (incorrect, should be 1545.60)
MaxYield      : 26880.00 (incorrect, should be 2688.00)
OverrideYield : 2688.00  (correct)

I am banging my head against the wall on this one, what
could be causing the incorrect values being inserted?

 

[tlbroadbent]

You didn't post the contents of columns after column 7. Do you have the correct starting position and length defined for the input file? It appears that error is caused by getting too many characters because the columns are ot 10 characters long in all cases. If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

 

[Mrbaseball34]

HUH???
The 1-7 numbers at the top were supposed to LINE UP with the
0's to look like this. They are not field numbers.

1         2         3
012345678901234567890

There ARE 13 columns in the data as well as the
transformation.

YES all starting pos and lengths are correct.

 

[tlbroadbent]

The result you get and the sample posted in the other thread don't show the text file columns as 10 characters. If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

 

[Mrbaseball34]

Look again...