Client-specific DTS Scripting Error

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

Client-specific DTS Scripting Error

Author

Topic

dairec
Starting Member

16 Posts

Posted - 2007-03-21 : 10:53:40

Hi there.

I have a .net Windows client/server application that calls a DTS package from the client.
One of the tasks in this package is a DTS Data Pump task that has an ActiveX transformation for cleaning data during transfer from an Excel2000 source worksheet to a SQL 2k database table.

My problem is that the package runs successfully on three machines but not on a fourth.
The problematic machine is running Windows2000 Pro while the others are on WinXP Pro, however the Windows Scripting Host version is the same on both: 5.6.

Any ideas why this may work on one client and not on another?

Thanks,
Daire

The error in the log is:


Error Source: Microsoft Data Transformation Services (DTS) Data Pump
Error Description:Error Code: 0
Error Source= Microsoft VBScript runtime error
Error Description: Type mismatch: 'CDbl'

The transformation script is:

Function Main()
	Dim currencyCode
	Dim currencyRate

	' Get the currency Code
	' The code is usually proceeded by a character of Ascii 160 so strip this off now if present.
	currencyCode = Trim(DTSSource("Ccy"))
	If Asc(Left(currencyCode, 1)) = 160 Then 
		currencyCode = Right(currencyCode, Len(currencyCode) - 1)
	End If
	If currencyCode = "EUR" Then
		DTSDestination("MarginCalls") = CDbl(DTSSource("Margin"))
		DTSDestination("UnrealisedProfitLoss") = CDbl(DTSSource("Unrealised P&L"))
		DTSDestination("NetCash") = CDbl(DTSSource("Total Equity"))
	Else
		' This is in a non-euro currency so get the rate and convert the balances
		currencyRate = CDbl(DTSLookups("GetCurrencyRate").Execute(currencyCode))

		DTSDestination("MarginCalls") = CDbl(DTSSource("Margin")) * currencyRate
		DTSDestination("UnrealisedProfitLoss") = (CDbl(DTSSource("Unrealised P&L")) * currencyRate)
		DTSDestination("NetCash") = (CDbl(DTSSource("Total Equity")) * currencyRate)
	End If

	Main = DTSTransformStat_OK
End Function

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2007-03-21 : 12:12:41

any chance it's not a number in the Cdbl field?
...ie the spreadsheet doesn't contain the same number of columns or columns aren't in the same order?
or you are encountering rows which don't fit the pattern.

the client o/s can be taken out of the equation by loading the problematic file from a different (currently working) client.

dairec
Starting Member

16 Posts

Posted - 2007-03-21 : 12:29:50

Hi Andrew, thanks for that.

I forgot to include the problematic row in the source (as specified in the error log):
" ( D O L 1 0 1 ) | - 2 , 6 2 5 . 0 0 | 1 6 , 1 8 7 . 5 0 | - 3 , 2 3 7 . 5 0 | G B P | 3 3 , 3 3 3 . 8 7 |"

I realise that there are spaces and other characters in the number fields, however one of the points of the task is to get around this problem by cleaning the data up.
Also, the same source data works fine on the other machines: note that the source file is on the database server and so does not differ between the client installations.

Daire

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2007-03-22 : 05:50:28

I've seen this 'extra-spacing' before with some text files (not excel...is there some export routine involved here?). I think it's to do with unicode files....they involve double-byte characters sets? Any chance you have different regional settings on the PC's?

do the spaces exist when viewed using a hex editor? (editpadpro is quite good)

dairec
Starting Member

16 Posts

Posted - 2007-03-22 : 12:15:08

Hi Andrew,

After doing a bit of MsgBox debugging I've found about a bit more.
The line it's failing on is CDbl(x) where x=" -2,625.00". This call works on the WinXP machines where IsNumeric(x) returns True but not on the Win2k machines where it returns False.
However, the first character is not a white-space but is in fact ASCII character 0xA0, or 160 in decimal. As you can see from the script, I encounter this trailing character in the first column as well, and have to strip it off before proceeding. I could make the same check with all the values but I would prefer to know why there is a difference in the scripting behaviour of the client machines.

The fact that it is an obscure character that is causing the problem leads me to suspect you may be right about the Unicode issue but the regional settings are the same on the working and non-working PCs; they are both set to Ireland. I wonder is there something fundamentally different in the Unicode set-up between WinXP and Win2k?

Daire

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2007-03-22 : 14:49:40

Last Q - Dunno. There are known issues with "IsNumeric"....the implementation in VB + SQL effectively allows unusual/non-predictable numbers...ie 4E7, etc, scientific format...search here for previous examples and possible better workarounds.

Off-topic - where are you?...see my profile!

dairec
Starting Member

16 Posts

Posted - 2007-03-23 : 06:21:37

Just to be clear when I did the IsNumeric test I meant VBScript IsNumeric, not T-SQL ISNUMERIC.
I've reduced the problem to a little bit of script I run in Internet Explorer - see below.
But I still haven't made any headway on why there's a difference in behavior.

BTW I'm from Dublin. And you?


<HTML>
<BODY onload="Test()">

<SCRIPT LANGUAGE=vbscript>
sub Test()
dim num
'num = "-123"	' This always returns True
num = Chr(160) & "-123"	' This returns True in WinXP but False in Win2K  
alert (IsNumeric(num))
end sub 
</SCRIPT>

</BODY>
</HTML>

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2007-03-23 : 11:55:38

20years working in a large bank in Ballsbridge...but from Tipp.

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2007-03-23 : 11:57:35

re the problem...you're hitting the edge of my knowledge in this area....maybe try posting/searching on some other sites...sqlservercentral.com, dbforums.com...or persuade others here to jump in.

dairec
Starting Member

16 Posts

Posted - 2007-03-23 : 12:03:49

Hi Andrew,

I posted the query on visualbasicscript.com and got a helpful response from a poster there. I've pasted it in below.
He is correct about the datasource: my source file is Excel but the data was originally pasted in from a webpage and having examined the HTML source there is indeed a leading in each cell.

The regional settings for the two PCs are the same so I'm still not sure whay there should be a difference in the interpretation of the data but I will try the Regular Expressions solution.

Thanks again,
Daire

quote:

Hi dairec,

the reason for your problem is that 160 means "non breaking space" in unicode, but
different 'things' (e.g. á - a with acent grave) in french/european codepages). VBScript
functions like IsNumeric() or Trim() will treat it a kind of blank or a kind of non-blank
character depending on Locale/Regional settings/low level utility functions.

The cause for your problem is that you put data with leading (trailing?) whitespace
into your database. Maybe you got the data from a HTML cell where was
used instead of cellpading?

The remedy would be to use a Regexp with a Pattern like "^[\s\aA0]*" to specify
all known whitespace (\s) and chr( 160 ) (\xA0) at the start of the data (^) to
remove the match in a context where you know that chr( 160 ) shouldn't be
interpreted as a legal non-blank character.

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2007-03-23 : 12:18:24

Grand....at least you can move on now to better things now (ie the mext problem)
Stick around for other SQL problems....we're not a bad bunch of dopes (oooops volunteers)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources