I have been tasked with importing a file that has various formats on each
line which collectively can represent a single record (produced from a
mainframe).

Typically, interface files I've worked with are tabular (like a spreadsheet)
with each row representing one item (fact) of data. Example:
ROD, BAUTISTA, 573-32-2343, 8951254, PIMCO SMALL CAP, EQUITY, PURCHASE, 200,
6/30/2004

YOUSE, GUIA, 546-34-3453, 8951254, PIMCO SMALL CAP, EQUITY, SALE, 240,
6/24/2004

The file I am attempting to work with has the data is laid out in a manner
where the single "fact" is represented on multiple lines. Example:

AST1 8951254 PIMCO SMALLCAP EQUITY

TRX1 ROD BAUTISTA 573-32-2343

TRX2 PIMCO SMALLCAP

TRX3 PURCHASE 200 6/30/2004

TRX1 YOUSE GUIA 546-34-3453

TRX2 PIMCO SMALL CAP

TRX3 SALE 240 6/24/2004


I'm just polling others for advice/suggestions on working with such a file
without purchasing new ETL software. Any ideas would be greatly
appreciated.

In article <OGjIyi3YEHA.1448 (AT) TK2MSFTNGP12 (DOT) phx.gbl>, Rod Bautista wrote:
You are looking at a custom routine here for sure. The standard text file
driver will hate you for the format.

Can you say

Here is the header row and here are the detail rows with that header?

If you can then you could write a custom task to do this

If you import the file then remember that a table is an unordered set so
unless you specify ORDER BY (Not possible with a text file) then you cannot
guarantee what will be read in and in what order.

A Custom task solution will allow you to work with the text file reading each
line as it is encountered.


Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org