I have an input file that is fixed field except for the last column, which
is variable length. A {CR}{LF} terminates each record at the point where it
ends. The record does not end at the same column because the following
spaces have been stripped from the last column. I do not a character that
can be used for delimited, so I cannot bring it is as delimited.

1001AB Acme Computing{CR}{LF}
1241CD Zips Inc.{CR}{LF}

How can I get DTS to process the input and recognize the {CR}{LF} as the
last column delimiter as well as the record delimiter?

Thanks.

SQL blurp:

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[tmp_scratch]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
drop table [dbo].[tmp_scratch]
create table dbo.tmp_scratch (fld1 char(4), fld2 char(4), fld3
char(500))
INSERT INTO dbo.tmp_scratch (fld1, fld2, fld3)
SELECT * FROM OPENROWSET('MSDASQL', 'Driver={Microsoft Text Driver
(*.txt; *.csv)};DefaultDir=D:\;','SELECT * FROM test.txt')

make a schemi.ini file in the "DefaultDir" folder containing this:
[TEST.TXT]
Format=FixedLength
CharacterSet=ANSI
ColNameHeader=False
Col1=client_code Text Width 4
Col2=client_sub_code Text Width 4
Col3=client_name Text Width 500

Hope this helps - warning the credentials to execute the OPENROWSET
against the text file driver (i.e. a dynamic data source leaves risk)
are a bit strict - likely need to SA privs or knowledge on how to
enabled it for a SQLServer account.

http://www.sqldts.com/
http://rickhathaway.blogspot.com/

On 26 Apr 2006 12:48:54 -0700, Slice wrote:

Exactly what I needed. I am off to test it now. Thank you.

Mike.