I have to create a DTS package that will import a file into my database.
This file can come from a Unix machine or a DOS machine so the row
delimiter will be either {LF} or {CR}{LF}. I know I can write my package
to handle either of these row delimiters, but can I write it so that it
will handle both?

--
Message posted via http://www.sqlmonster.com

Hi Rob,

"Rob Booth" wrote:
you can dynamicly change the row delimiter of the text file connection to handle this.
1. you have to check which fileformat you have - maybe with an activeX task reading as much characters from that file to be save to
find the first newline in there to check the format.
2. Change the Row delimiter property of the text file connection according to the found format, e.g.
Set oConn = DTSGlobalVariables.Parent.Connections("Text File (Source)")
oConn.ConnectionProperties("Row Delimiter").Value = Chr(10)
Set oConn = Nothing

But another question is the character set of that file, which could cause trouble when importing text with extended character set.
Do you checked this?

HTH
Helge

Thanks for the information. The character sets in the files should both be
standard ASCII. Do you have any thoughts on how I'd check the format of
the file in ActiveX?

Thanks Again.

--
Message posted via http://www.sqlmonster.com

Hi Rob,

"Rob Booth via SQLMonster.com" wrote:
here is a small code sample that hopefully explain how to check it

---------------------------
intCrLf = 0
intLf = 0
Set fso = CreateObject("Scripting.FileSystemObject")
Set oFileIn = fso.OpenTextFile(fileIn)

'Here we read 1000 characters as long as we find CrLf or Lf
Do While ((Not oFileIn.atEndOfStream) AND intCrLf = 0 AND intLf = 0)
Text = oFileIn.Read(1000)
intCrLf = Instr(1,Text,vbCrLf)
intLf = Instr(1,Text,vbLf)
Loop
' now we can decide which filetype we have
If (intCrLf > 0) Then 'DOS
....
Else If (intLf > 0) Then 'Unix
....
-------------------
You should check for file existence first and handle errors right.
See http://www.sqldts.com/default.aspx?292 for examples for file handling

Helge