DTS: import badly-formatted file? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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)

DTS: import badly-formatted file?

Author

Topic

aiken
Aged Yak Warrior

525 Posts

Posted - 2002-02-27 : 13:40:22

I need to create a DTS package to regularly import an IRC server's nickserv DB into SQL server. The nickserv is a text file in the messed up format:


n nickname password fullname lastdns
e email
l hostmask
i garbage garbage garbage

...that repeats over and over. Not every user has an "l" record, and some users have two or more of them.

I need to extract nickname, password, and email only, at least for now. I've figured out how to do the import using a VB transformation script to just get the "n" rows, and skip everything else. Here's that script:


Function Main()
  if DTSSource("Col001")="n" Then
	DTSDestination("nick") = DTSSource("Col002")
	DTSDestination("password") = DTSSource("Col003")
	DTSDestination("last_dns") = DTSSource("Col005")
	Main = DTSTransformStat_OK
  Else
     Main=DTSTransformStat_SkipRow
  End If
End Function

However, I can't seem to figure out how I can grab the email info and associate it with the row of the table that was previously inserted. The order of the file is guaranteed to always be at least "n/e", so there's no danger of getting an e before an n.

Any ideas?

Thanks
-b

MichaelP
Jedi Yak

2489 Posts

Posted - 2002-02-27 : 14:05:20

Well you could get the nickname password fullname lastdns fields into variables, then get the email into a variable after you read the next row, and then when len(sEmail) > 0 then insert into table.....
then reset all of your variables to null or empty, and keep reading the file.

Michael

aiken
Aged Yak Warrior

525 Posts

Posted - 2002-02-27 : 15:33:04

Thanks... this worked perfectly.

-b

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources