SQL Server 200

Help! Surely this has happened to others before me. A new customer wants to send updates in a fixed-width txt file in which master and detail rows alternate

How do you do this

Do you
1) Painfully muck around with 100,000 rows in the .txt file first--split it into 2 files, one for master records and one for detail records and then process? If so, what do you use? I'd probably hack at it with a VB Script module in a DTS package

o

2) Is there a way to feed it into 2 tables where rows starting with x go to one table and rows starting with y go to another

The plan was to use a fast and dirty DTS package to shove this stuff into a table (probably 2 but we might just toss the stuff we don't need and put in in one) but I'd like some advice on how to proceed

Thanks for any suggestions!

Yours is such a common situation that I am puzzled that Microsoft has not addressed the issue

I needed to parse a ACH file. It uses a file in which there are files that contain batches that contain records that contain one other bit of information. I ended up using a Visual Basic program with regular expressions to parse this file

Russ

Could you run an Exe or even build a custom task that will acept a filename
as the input and will output the n different types of file you require.
You then pick those files up further through the package. You could loop
through a directory doing this.

--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.allisonmitchell.com - Expert SQL Server Consultancy.
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org


<Russel Loski>; "MCSD" <RLoski (AT) NSPMhotmail (DOT) com> wrote

information. I ended up using a Visual Basic program with regular
expressions to parse this file.

Here's the plan

I start by creating two data transformation tasks which pump rows out to one of two Temp tables by parsing the first character of the row (m for master, d for detail is how they are coded). I get blank rows in each table for the rows I don't process but they are easy to delete. I also parse out the SNN for each row and put that in a separate field in the table. Then I run a stored procedure that moves the child records into column 2 of the table with the master records matching SSNs as it goes. This takes a while with 98,000 records

Then I go back to the Master table and parse columns 1 and 2 into the appropriate fields in the destination table by splitting the strings in the columns. (In the end it is better for me to have all info in one record in my destination.

It isn't pretty, but it seems that it will work

Any better ideas

In message <C139C2D9-3502-40E9-8233-2B5F5F3F94AC (AT) microsoft (DOT) com>, "Russel
Loski, MCSD" <RLoski (AT) NSPMhotmail (DOT) com> writes
Have you made a wish for future versions? (sqlwish (AT) microsoft (DOT) com). Don't
expect a response, but it a real address and PMs and Devs read it and do
take note. Make your case to them.

I think it should be addressed too, but don't have any examples of my
own.
Is ACH a third-party app?

--
Darren Green (SQL Server MVP)
DTS - http://www.sqldts.com

PASS - the definitive, global community for SQL Server professionals
http://www.sqlpass.org