simple DTS active X script

[Holz]

I am trying to transform a column with DTS as it is coming. Basically I have a source field that was supposed to be numeric but someone put in charaters. I need to convert these fileds to a numberic value so the will go into my smallint destination field.
So the source fields contain tha actual value 'XXXX' and I need to convert it to 0000.
This is what I have written but I get a syntax error on line 1 when I run this. Any help would be greatly apprecaited.

Function Main()

If DTSSource("dma&#34 = 'XXXX' then
DTSDestination("DMA&#34 = 0000
Else
DTSDestination("DMA&#34 = DTSSource("dma&#34
End if

Main = DTSTransformStat_OK
End Function

[Dano]

Just noticed that you want to put the data into a smallint, but it looks like your source is 4-position 'xxxx'. 4-place could be 9999, larger than a smallint would allow (255). Also, you can't put 0000 into a smallint either. It's just a 0 (one of 'em). As you work on this problem of yours, keep in mind you may need to use a function such as "convert" to make things work smooth.

Dano.


------------
Holz at 8/27/99 11:30:08 AM

I am trying to transform a column with DTS as it is coming. Basically I have a source field that was supposed to be numeric but someone put in charaters. I need to convert these fileds to a numberic value so the will go into my smallint destination field.
So the source fields contain tha actual value 'XXXX' and I need to convert it to 0000.
This is what I have written but I get a syntax error on line 1 when I run this. Any help would be greatly apprecaited.

Function Main()

If DTSSource("dma&#34 = 'XXXX' then
DTSDestination("DMA&#34 = 0000
Else
DTSDestination("DMA&#34 = DTSSource("dma&#34
End if

Main = DTSTransformStat_OK
End Function

[Holz]

A tinyint has the 255 limit. I am using a smallint which can go up to 32,767. I am aware that it will appear as 0, but I still need a way to convert the character value from XXXX to a numeric 0.

Holz


------------
Dano at 8/31/99 5:59:49 PM

Just noticed that you want to put the data into a smallint, but it looks like your source is 4-position 'xxxx'. 4-place could be 9999, larger than a smallint would allow (255). Also, you can't put 0000 into a smallint either. It's just a 0 (one of 'em). As you work on this problem of yours, keep in mind you may need to use a function such as "convert" to make things work smooth.

Dano.


------------
Holz at 8/27/99 11:30:08 AM

I am trying to transform a column with DTS as it is coming. Basically I have a source field that was supposed to be numeric but someone put in charaters. I need to convert these fileds to a numberic value so the will go into my smallint destination field.
So the source fields contain tha actual value 'XXXX' and I need to convert it to 0000.
This is what I have written but I get a syntax error on line 1 when I run this. Any help would be greatly apprecaited.

Function Main()

If DTSSource("dma&#34 = 'XXXX' then
DTSDestination("DMA&#34 = 0000
Else
DTSDestination("DMA&#34 = DTSSource("dma&#34
End if

Main = DTSTransformStat_OK
End Function