Help with DTS Transformation Script

Mrbaseball34 · Mar 13, 2003

I have this data in a fixed-length text file.
(there are 250,000 records)

1 2 3 4 5
1234567890123456789012345678901234567890123456789012345
2001010011001997002000330000000825000000132000000013200
2001010011001997003000330000000825000000132000000013200
2001010011001997 000330000000825000000132000000013200
2001010011001 000330000000825000000132000000013200

***********************************************************************
Here is the file schema:

Fieldname CharPos
------------- -------
ReinYear : 1-4
StateCode : 5-6
CropCode : 7-10
CountyCode : 11-13
TypeCode : 14-16
PracticeCode : 17-19
HighYield : 20-25
WarnYield : 26-35
MaxYield : 36-45
OverrideYield : 46-55

***********************************************************************

I need a DTS script to import these fields in this format:
HighYield 9999.9
WarnYield 99999999.99
MaxYield 99999999.99
OverrideYield 99999999.99
Example

( the first row above:
2001010011001997002000330000000825000000132000000013200
)
HighYield 33.0
WarnYield 82.50
MaxYield 132.00
OverrideYield 132.00

***********************************************************************
I've tried this but it gives me syntax errors:
(in the Replace, I presume)

Function Main()
DTSDestination("ReinYear&quot

= DTSSource("Col001&quot

DTSDestination("StateCode&quot

= DTSSource("Col002&quot

DTSDestination("CropCode&quot

= DTSSource("Col003&quot

if Len(Trim(DTSSource("Col004&quot

)) > 0 then
DTSDestination("CountyCode&quot

= DTSSource("Col004&quot

else
DTSDestination("CountyCode&quot

= NULL
end if
if Len(Trim(DTSSource("Col005&quot

)) > 0 then
DTSDestination("TypeCode&quot

= DTSSource("Col005&quot

else
DTSDestination("TypeCode&quot

= NULL
end if
if Len(Trim(DTSSource("Col006&quot

)) > 0 then
DTSDestination("PracticeCode&quot

= DTSSource("Col006&quot

else
DTSDestination("PracticeCode&quot

= NULL
end if
DTSDestination("HighYield&quot

= Replace(FormatNumber(DTSSource
("Col007&quot

,1), ',', '', 1, -1, 1)
DTSDestination("WarnYield&quot

= Replace(FormatNumber(DTSSource
("Col008&quot

,2), ',', '', 1, -1, 1)
DTSDestination("MaxYield&quot

= Replace(FormatNumber(DTSSource
("Col009&quot

,2), ',', '', 1, -1, 1)
DTSDestination("OverrideYield&quot

= Replace(FormatNumber(DTSSource
("Col010&quot

,2), ',', '', 1, -1, 1)
DTSDestination("OverrideFlag&quot

= DTSSource("Col011&quot

DTSDestination("UserId&quot

= DTSSource("Col012&quot

DTSDestination("Moddate&quot

= DTSSource("Col013&quot

Main = DTSTransformStat_OK
End Function

***********************************************************************

What am I doing wrong, here and how do I get my data imported correctly using DTS???

nowayout · Mar 13, 2003

is the data in the text file are all continuos or can it be separated by comma, ;, or tab

Mrbaseball34 · Mar 13, 2003

It is all continuous, sent to us by a government agency so
we have no access to the format.

Mrbaseball34 · Mar 14, 2003

I modified to this:
Function Main()
DTSDestination("ReinYear&quot

= DTSSource("Col001&quot

DTSDestination("StateCode&quot

= DTSSource("Col002&quot

DTSDestination("CropCode&quot

= DTSSource("Col003&quot

if Len(Trim(DTSSource("Col004&quot

)) > 0 then
DTSDestination("CountyCode&quot

= DTSSource("Col004&quot

else
DTSDestination("CountyCode&quot

= NULL
end if
if Len(Trim(DTSSource("Col005&quot

)) > 0 then
DTSDestination("TypeCode&quot

= DTSSource("Col005&quot

else
DTSDestination("TypeCode&quot

= NULL
end if
if Len(Trim(DTSSource("Col006&quot

)) > 0 then
DTSDestination("PracticeCode&quot

= DTSSource("Col006&quot

else
DTSDestination("PracticeCode&quot

= NULL
end if
DTSDestination("HighYield&quot

= Left(DTSSource("Col007&quot

, 5) & "." & Right(DTSSource("Col007&quot

, 1)
DTSDestination("WarnYield&quot

= Left(DTSSource("Col008&quot

, 8) & "." & Right(DTSSource("Col008&quot

, 2)
DTSDestination("MaxYield&quot

= Left(DTSSource("Col009&quot

, 8) & "." & Right(DTSSource("Col009&quot

, 2)
DTSDestination("OverrideYield&quot

= Left(DTSSource("Col010&quot

, 8) & "." & Right(DTSSource("Col010&quot

, 2)
DTSDestination("OverrideFlag&quot

= DTSSource("Col011&quot

DTSDestination("UserId&quot

= DTSSource("Col012&quot

DTSDestination("Moddate&quot

= DTSSource("Col013&quot

Main = DTSTransformStat_OK
End Function

But the numbers come up like this:

{for This line imported}
2001010011001997002000330000000825000000132000000013200

HighYield 00033.
WarnYield 000000825.
MaxYield 000013200.
OverrideYield 000013200.

tlbroadbent · Mar 14, 2003

See my reply in thread183-500979. Looking at the data, the columns seem to be 9 characters wide but your code is rtying to handle 10 characters. That won't work. 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 · Mar 14, 2003

You wouldn'
t happen to be an Aggie, would you? <bg>

I count TEN chars in the column.

WarnYield : 26-35
pos 26 - 1
pos 27 - 2
pos 28 - 3
pos 29 - 4
pos 30 - 5
pos 31 - 6
pos 32 - 7
pos 33 - 8
pos 34 - 9
pos 35 - 10

MaxYield : 36-45
pos 36 - 1
pos 37 - 2
pos 38 - 3
pos 39 - 4
pos 40 - 5
pos 41 - 6
pos 42 - 7
pos 43 - 8
pos 44 - 9
pos 45 - 10

OverrideYield : 46-55
pos 46 - 1
pos 47 - 2
pos 48 - 3
pos 49 - 4
pos 50 - 5
pos 51 - 6
pos 52 - 7
pos 53 - 8
pos 54 - 9
pos 55 - 10

tlbroadbent · Mar 14, 2003

I refer to your own sample, which you said didn't work. Explain to me this 10 column breakout could return the correct result.

{for This line imported}[tt]
7 char 10 chars 10 chars 10 chars?
2001010011001997002|0003300|0000082500|0001320000|0013200?
[/tt]
To get the results you want, you need to define the columns as follows.
[tt]
7 char 9 chars 9 chars 11 chars
2001010011001997002|0003300|000008250|000001320|00000013200|
[/tt]

So you have to decide. If the columns are ten characters long then DTS is giving you the correct result. If the columns are only 9 characters long, you ned correct the DTS package.

BTW, I am an Aggie from Utah State University. 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 · Mar 14, 2003

Nope, look again.... ;-)

Code:

         1         2         3         4         5
1234567890123456789012345678901234567890123456789012345
2001010011001997002000330000000825000000132000000013200
RRRRssCCCCccctttPPPhhhhhh[URL unfurl="true"]WWWWWWWWWWmmmmmmmmmmOOOOOOOOOO[/URL]

FieldName       From/To	Length   DTSColumn
------------------------------------------
ReinYear      : 1-4    	  4      Col001
StateCode     : 5-6    	  2      Col002
CropCode      : 7-10   	  4      Col003
CountyCode    : 11-13  	  3      Col004
TypeCode      : 14-16  	  3      Col005
PracticeCode  : 17-19  	  3      Col006
HighYield     : 20-25  	  6      Col007
WarnYield     : 26-35  	  10     Col008
MaxYield      : 36-45  	  10     Col009
OverrideYield : 46-55  	  10     Col010

I'm not importing Cols 11, 12, and 13 anymore.

Tell ya' what, let's drop this thread and continue in the other because it is has the most recent code.

tlbroadbent · Mar 14, 2003

Sorry, I misread the VB script and was using 7 for the length of the HighYield column. Sorry for the confusion.

Try this simple transformation.

DTSDestination("HighYield&quot

= DTSSource("Col007&quot

/100.
DTSDestination("WarnYield&quot

= DTSSource("Col008&quot

/100.
DTSDestination("MaxYield&quot

= DTSSource("Col009&quot

/100.
DTSDestination("OverrideYield&quot

= DTSSource("Col010&quot

/100.

You may need to convert the columns to numeric first.

DTSDestination("HighYield&quot

= CSng(DTSSource("Col007&quot

)/100.
DTSDestination("WarnYield&quot

= CSng(DTSSource("Col008&quot

)/100.
DTSDestination("MaxYield&quot

= CSng(DTSSource("Col009&quot

)/100.
DTSDestination("OverrideYield&quot

= CSng(DTSSource("Col010&quot

)/100.
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 · Mar 14, 2003

I can't do Col007 like that due to it being only one
decimal place. Should CSng(DTSSource("Col007&quot

)/10 work?

BTW, I wrote a VBA script and placed it in Excel and it worked perfectly with the EXACT SAME code as the DTS script.

Of course I used cell values instead of column values.

tlbroadbent · Mar 14, 2003

I'm not sure why your code isn't working. Given my recent failing at reading the code properly, I don't want to attempt any further analysis at this time. Dividing by 10 wil be fine for col007. 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 · Mar 14, 2003

DTSDestination("HighYield&quot

= CStr(CSng(DTSSource("Col007&quot

)/10)
if (CLng(strVar) = 99999999) then
DTSDestination("WarnYield&quot

= NULL
else
DTSDestination("WarnYield&quot

= CStr(CSng(DTSSource("Col008&quot

)/100)
end if
DTSDestination("MaxYield&quot

= CStr(CSng(DTSSource("Col009&quot

)/100)
DTSDestination("OverrideYield&quot

= CStr(CSng(DTSSource("Col010&quot

)/100)

Well, that didn't work either as this is the result for
this record:

Code:

         1         2         3         4         5     
1234567890123456789012345678901234567890123456789012345
2002010021001997   006720000015456000002688000000268800

HighYield     : 672   (incorrect, should be 672.0)
WarnYield     : 15456 (incorrect, should be 1545.60)
MaxYield      : 2688  (incorrect, should be 2688.00)
OverrideYield : 2688  (incorrect, should be 2688.00)

Remember the layout:
HighYield     : 20-25        6      Col007
WarnYield     : 26-35        10     Col008
MaxYield      : 36-45        10     Col009
OverrideYield : 46-55        10     Col010

tlbroadbent · Mar 14, 2003

Are the SQL Server columns defined as character data? Are you really storing numbers as strings?

If the SQL column is numeric, omit the CStr conversion. In the division by 100 try adding a decimal point as I recommended. CSng(DTSSource("Col008&quot

)/100. 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 · Mar 14, 2003

OK, This is a temp table where the data is stored as strings. I then run another DTS to import into the real table where the data is numeric 5(9,2)

I've tried doing it directly into the ADMaxYield Table and I
couldn't get it to work there either.

tlbroadbent · Mar 14, 2003

Cases 1,3 and 4 are explainable. Cstr will remove trailing zeros and decimals as well as leading zeros from a numeric value. Yuo can usethe Formatnumeric function instead of Cstr. It allows you to specify the number of decial digits and whether to include leading digits.

DTSDestination("HighYield&quot

= FormatNumeric(DTSSource("Col007&quot

/10.)
DTSDestination("WarnYield&quot

= FormatNumeric(DTSSource("Col008&quot

/100.)
DTSDestination("MaxYield&quot

= FormatNumeric(DTSSource("Col009&quot

/100.)
DTSDestination("OverrideYield&quot

= FormatNumeric(DTSSource("Col010&quot

/100.)

I don't know why 15456 would be off by a factor of ten unless the column value was being divided by 10. 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 · Mar 14, 2003

Wouldn't that be FormatNumber, instead? I found that it didn't work for me either...

I don't know what I'm going to do without having to write an
import program in Delphi, which is what I usually develop in.

tlbroadbent · Mar 14, 2003

Perhaps, it would be best for me to stop posting, today.I not only spelled the function name incorrectly but also left off the formatting parameter.

DTSDestination("HighYield&quot

= FormatNumber(DTSSource("Col007&quot

/10.,"0.0")
DTSDestination("WarnYield&quot

= FormatNumeric(DTSSource("Col008&quot

/100.,"0.00")
DTSDestination("MaxYield&quot

= FormatNumeric(DTSSource("Col009&quot

/100.,"0.00")
DTSDestination("OverrideYield&quot

= FormatNumeric(DTSSource("Col010&quot

/100.,"0.00") 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 · Mar 17, 2003

Thought you might like to see the final script...

I am importing directly into the ADMaxYield table now instead of going through the MaxYieldImport table.

Code:

Function Main()
Dim strVar
Dim strLeft
Dim strRight
	DTSDestination(&quot;ReinYear&quot;) = DTSSource(&quot;Col001&quot;)
	DTSDestination(&quot;StateCode&quot;) = DTSSource(&quot;Col002&quot;)
	DTSDestination(&quot;CropCode&quot;) = DTSSource(&quot;Col003&quot;)
	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
	strLeft = cStr(cLng(Left(DTSSource(&quot;Col007&quot;), 5)))
	strRight = Right(DTSSource(&quot;Col007&quot;), 1)
	strVar = strLeft & strRight 
	DTSDestination(&quot;HighYield&quot;) = FormatNumber(strVar * .1, 1, 0, 0, 0)

	strLeft = cStr(cLng(Left(DTSSource(&quot;Col008&quot;), 8)))
	if (CLng(strLeft) = 99999999) then
		DTSDestination(&quot;WarningYield&quot;) = NULL
	else
		strRight = Right(DTSSource(&quot;Col008&quot;), 2)
		strVar = strLeft & strRight 
		DTSDestination(&quot;WarningYield&quot;) = FormatNumber(strVar * .01, 2, 0, 0, 0)
	end if

	strLeft = cStr(cLng(Left(DTSSource(&quot;Col009&quot;), 8)))
	strRight = Right(DTSSource(&quot;Col009&quot;), 2)
	strVar =strLeft & strRight 
	DTSDestination(&quot;MaxYield&quot;) = FormatNumber(strVar * .01, 2, 0, 0, 0)

	strLeft = cStr(cLng(Left(DTSSource(&quot;Col010&quot;), 8)))
	strRight = Right(DTSSource(&quot;Col010&quot;), 2)
	strVar =strLeft & strRight 
	if (Len(Trim(DTSSource(&quot;Col010&quot;))) > 0) then
  		DTSDestination(&quot;OverrideYield&quot;) = FormatNumber(strVar * .01, 2, 0, 0, 0)
	else
		DTSDestination(&quot;OverrideYield&quot;) = NULL
	end if

	if (Len(Trim(DTSSource(&quot;Col011&quot;))) > 0) then
		DTSDestination(&quot;OverrideFlag&quot;) = DTSSource(&quot;Col011&quot;)
	else
		DTSDestination(&quot;OverrideFlag&quot;) = NULL
	end if
	Main = DTSTransformStat_OK
End Function

Thanks for the help...

MB34

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Help with DTS Transformation Script

Programmer

Programmer

Programmer

Programmer

MIS

Programmer

MIS

Programmer

MIS

Programmer

MIS

Programmer

MIS

Programmer

MIS

Programmer

MIS

Programmer

Similar threads

Log in

Part and Inventory Search

Sponsor