Solved

DTS - Vbscript transformation

Posted on 2000-04-12
14
1,127 Views
Last Modified: 2013-11-30
One column in a DTS package (Excel to SQL Server 7) contains
zipcode.   The column is defined as varchar 5 in the SQL Server
table. Any zero's in the first position of the zipcode get
truncated, so for example, 03341 would end up as 3341 in
the destination table.
What vbscript code could I use  to transform this correctly?
0
Comment
Question by:vd
  • 6
  • 5
  • 2
  • +1
14 Comments
 
LVL 2

Expert Comment

by:DawsonB
ID: 2708429
Try this

Function Main()
dim zipcode

  ZipCode = DTSDataSource("zipcode")
  ZipCode = string(5-len(ZipCode),"0") & ZipCode
  DTSDestination("zipcode") = ZipCode
  Main = DTSTransformStat_OK

End Function
0
 
LVL 2

Expert Comment

by:DawsonB
ID: 2708435
Sorry, that should have read:
 (difference in DTSSource line)

Function Main()
dim zipcode

  ZipCode = DTSSource("zipcode")
  ZipCode = string(5-len(ZipCode),"0") & ZipCode
  DTSDestination("zipcode") = ZipCode
  Main = DTSTransformStat_OK

End Function
0
 

Author Comment

by:vd
ID: 2708479
This looks great, but I'm getting errors, apparently
on NULL's  in the source zipcodes.  
0
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 
LVL 2

Expert Comment

by:DawsonB
ID: 2708545
as a guss, try

Function Main()
dim zipcode

  ZipCode = DTSSource("zipcode")
  If Not Null(ZipCode) then
    ZipCode = string(5-len(ZipCode),"0") & ZipCode
  Else
    ZipCode = "" ' Or any "Default" value you prefer
  End If
  DTSDestination("zipcode") = ZipCode
  Main = DTSTransformStat_OK
End Function


This will put any entries in your source db in with an empty string in your destination table.

Hope this helps.


0
 

Author Comment

by:vd
ID: 2708546
Does the code above prefix every zipcode with
'O'? This wouldn't work if the zipcode does not have
zero in the first position.
0
 
LVL 2

Expert Comment

by:DawsonB
ID: 2708560
as an afterthought,
the lines

  Else
    ZipCode = Null
  End if

should work if you are allowing nulls in your destination table, but as a rule I never allow nulls in my db's unless there is no logical concept of a default value. For example, a field called BirthDay could have a default value of Null because if I don't know the true value, then some other value is more wrong than null, but
a Field of Name I would prefer an empty string ("" in vb, or '' in tsql) to Null.
0
 
LVL 2

Expert Comment

by:DawsonB
ID: 2708598
I am not familiar with US zip codes (assuming these are us?) but if all of the zip codes you are getting are supposed to be 5 digits long, this will put 0's in front of any other number, and leave you with zip codes all of 5 digits long. for examples the following would result.

Source        Destination
1243             01234
12345            12345
11               00011
9                00009
10000            10000


If you have zip codes that 'could' be shorter than 5 digits, then you may need to change the column properties in the Excel spreadsheet first to say a text, then manually adjust those zip codes which need a zero put on the front of them.
0
 

Author Comment

by:vd
ID: 2708881
I'll try out your suggestions and back get to you.
No, actually the NULLS are on the input file, appear
to be blank records - I'll have to look at that.
0
 

Author Comment

by:vd
ID: 2709555
Doesn't work. An error "invalid use of NULL" occurs,even when I eliminate most everything but
the  "ZipCode = string(5-len(ZipCode),"0") & ZipCode" statement.  
Can I stop processing when it gets to the NULL
records toward the end of the spreadsheet,
like the traditional "end of file" processing?
0
 
LVL 6

Expert Comment

by:crsankar
ID: 2710765
try using a string function to convert your dtssource data

In oracle you can use to_char. I dont know the equivalent in vb.
0
 
LVL 6

Expert Comment

by:crsankar
ID: 2710777
I think it is ctsr in vb.
0
 
LVL 2

Accepted Solution

by:
DawsonB earned 50 total points
ID: 2710873
Crsankar: That might work, the correct format in vb is
ZipCode = Cstr(ZipCode)


0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 2711083
Modify your line
ZipCode = DTSSource("zipcode")
into
ZipCode = DTSSource("zipcode").Value

Explanation:
VBScript is not really typed, and therefore has problems with the default properties
0
 

Author Comment

by:vd
ID: 2744782
We were able to avoid the problem by redefining
the column in excel to TEXT.  The approach
described here will be useful in the future!
0

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Solar Winds can't see SQL Server Express 17 33
Run an action on recently added records to a table 13 63
Where is the Help Section? 8 28
SQL Recursion 6 20
Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question