Okay, this seems very simple, but I'm unable to get it to work or find
documentation on how to do this anywhere.

I have an Excel spreadsheet I want to import into my MS SQL 2000
database using DTS. During the import, I want to devide one column by
another column from the source and place this value into the
destination. Here's what I'm trying:

Function Main()
DTSDestination("Total") = DTSSource("Total") / DTSSource("Aug")
DTSDestination("Aug") = DTSSource("Aug")
DTSDestination("Jul") = DTSSource("Jul")
DTSDestination("Jun") = DTSSource("Jun")
DTSDestination("May") = DTSSource("May")
DTSDestination("Apr") = DTSSource("Apr")
DTSDestination("DeptCode") = DTSSource("DeptCode")
Main = DTSTransformStat_OK
End Function

Nothing I've tried works, plus I've been reading through Wrox SQL
Server Pro DTS book and I can't find any reference on how to do this.
I can do TONS of text formulas, but no calcs like this.

Am I doing something wrong??? Surly this is possible during the
import, and if not, why??? This seems ery simple.

Thanks,

Alex.

What is the error ?
What is the datatype of DTSDestination("Total")

Here is what I did

CREATE TABLE Reception (col1 decimal(10,2)) --will receive my data

Excel spreadsheet looks like this

A B
234 12

The answer I am expectng is 19.5


My Active Script transform looks like

Function Main()
DTSDestination("col1") = CDbl(DTSSource("A")) / CDbl(DTSSource("B"))
Main = DTSTransformStat_OK
End Function

It works






--

----------------------------
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org



"Alex" <alex (AT) totallynerd (DOT) com> wrote

Hi,
This is probably because the values are being take as
VARCHAR/TEXT Instead of Numeric, This might happen if you
dont assume that the first row in excel is a header cauz
thats the way DTS treats excel the first row is always a
header.
Cheers
Ravi

"ravi" <sunshines27 (AT) rediffmail (DOT) com> wrote

Hi...

Actually I found the problem. Several of the values that I was
dividing by were zero, so it was erroring out. I added an If
statement in the DTS statement to determine if the dtssource was zero,
and if so, it didn't run the calculation.

As for field types, the spreadsheed was set to number and the
destination database fields were float... so datatype was fine.

Thanks for the feedback...

Alex.