Visual Basic Transformation Script

I'm having a problem with the code below in a DTS Transformation. It works
fine with any numbers that are in the "Dial" table that it references - but
if the dialing code is not there, and the telephone number is valid it bombs
out. Ideally i would like it to put "unknown" in the description and move on
to the next row

Is there any way to do this? I appologise in advance if this is something
simple!


Option Explicit
Function Main()
Dim DialCode
Dim FirstDigit
Dim SecDigit
Dim arrNumStuff
FirstDigit = Left( DTSSource("ClipNumber") , 1)
SecDigit = Mid( DTSSource("ClipNumber") , 2 , 1 )
DTSDestination("Called Number") = DTSSource("ClipNumber")
If DTSSource("Flag") = "O " then
If FirstDigit = 0 And SecDigit <> 0 then
DialCode = Left( DTSSource("ClipNumber") , 5 )
arrNumStuff = DTSLookups("NumDetails").Execute(DialCode)
DTSDestination("Call type") = arrNumStuff(0)
DTSDestination("Description") = arrNumstuff(1)
Else
DTSDestination("Call type") = Null
DTSDestination("Description") = "Local or Speaking Clock"
End If
Else
DTSDestination("Call type") = Null
DTSDestination("Description") = Null
End If
Main = DTSTransformStat_OK
End Function

The Lookup it references is as follows:

SELECT [Call Type], Destination
FROM Dial
WHERE ([Dialled String] = ?)

What about checking for if the array ISEMPTY() ?

--
--

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

"Adept Systems (Hull) Ltd" <JamesB (AT) adept-systems (DOT) co.uk> wrote

I Expect that would work except the package crashes out when it tries to
look for data to put into the array - as soon as it performs the lookup
"DTSLookups("NumDetails").Execute(DialCode)" it halts with an error
message - but all the rows before the one that it cant find in the "Dial"
table work fine

"Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com> wrote

Check the value of DialCode before you pass it in.



--
--

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

"Adept Systems (Hull) Ltd" <JamesB (AT) adept-systems (DOT) co.uk> wrote

in the paticular instance thats tripping up the whole thing it has 01472 in
it (a UK dialing code) - this isnt in the table so it just seems to grind to
a halt there

it works fine for mobile and international numbers basically which is what
we need but UK dialing codes are in not in the Dial table

"Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com> wrote

What is the error it gives exactly?
Turn logging on

If I tested using this

CREATE TABLE Source(SourceCode varchar(50)
CREATE TABLE Lookup(ID INT Primary key IDENTITY(1,1), LookupVal varchar(50))


In the lookup I would Pass in SourceCode looking for ID

Would that be a valid test ?

--
--

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

"Adept Systems (Hull) Ltd" <JamesB (AT) adept-systems (DOT) co.uk> wrote

i'm sorry i'm not sure what that code means - i've never used sql before -
my background is in PC Desktop Support

i've only ever created tables in enterprise manager

if it helps i could give you a list of the fields and data types in each of
the two tables?

"Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com> wrote

Ok that would be good.

Also give me same data that you expect in each table (Enough to make it
fail).

You do not need to give me the whole table def only the relevant columns



--
--

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

"Adept Systems (Hull) Ltd" <JamesB (AT) adept-systems (DOT) co.uk> wrote

Source Table
CalledNumber (varying lengths depending if dialing code was used - if no
dial code then local call or speaking clock)
Flag ("O " if was a valid chargable call - "IV" if not)

Dial Table
Dialing code
Description
Call Type

Destination File
Called Number
Number Description
Call Type

Sample data

Source
CalledNum 555555 07970555555 555 123 01472555555
Flag "O " "O " "IA" "O "
"O "

Dial Table
Dialing Code 07970 00353
Number Description Mobile Republic of Ireland
Call Type Voice Voice

Destination File
Called Number 555555 07970555555 123
Number Description Local or SC Mobile Local or SC
Call Type Voice

when i run a test on the transformation from the script window it says "1
task(s) failed during execution" but it allows you to view results and shows
the output is succesfull upto the point where it tries the dial code "01472"

Hope this makes sense!


"Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com> wrote