design advice...writing a text file

I need to create a text file using information from SQL tables/views in the following format...Can anyone recommend a direction or procedure to look into, i.e, sql script, custom dts, etc. The items in parentheses identify specific portions of the text file.

(01)
101081,84423,customer ,072304,customer ,11310 Via Playa De Cortes , ,San Diego ,CA,92124,
(02) 6 ,1 , , , , ,22 ,1 ,0.00 ,160.46 ,160.46 ,0.00 , , , , , , , , ,1,1
(03)B130907540,5.41 ,1
(03)B130907550,5.41 ,1
(03)B130907560,5.41 ,1
(03)B130907570,6.04 ,1
(03)B065007550,1.72 ,2
(03)B065007560,1.72 ,6
(03)B519926530,4.66 ,13
(03)B519926550,4.66 ,12
(03)B560911200,2.14 ,1
(03)B560912500,2.14 ,1
(03)B095305750,3.65 ,1This looks a lot like EDI format to me. Maybe it is just because of all the bad memories of it. Given a choice, I would go with a scripting language outside of SQL Server. Either PERL or VB Script. I believe PERL was designed with such file formats in mind, and it is not that hard to learn.|||Perl would make the solution easier to code. VBA could be incorporated into a DTS package, which would be a bunch more portable (and easier to write if you already know VB and don't know Perl).

Pick your poison. Either Perl or VBA would work nicely, and each has its own benefits.

-PatP|||Actually EDI (X12) looks more like this:

CAS*PR*1*24**2*12~CAS*CO*45*40~...etc., all one line.|||Could well be I have the wrong name for it, then. Like I say, it has been a while. The format I had to deal with was:

header row
first item header
first item detail
first item detail
first item footer
second item header
...
...
footer row.

A very nested and finicky format. In my first job, I spent a number of weeks trying to get an output that would work, but kept getting blank lines in my output. Nowadays, I look back on that and laugh. Probably take an hour with different tools. Back then, I was truly "a man with a hammer".|||The fastest way to get data out of SQL to text is using a BCP (bulk copy paste). I've written a few EDI formats for medicare/medicaid billing and such using BCP and it's works like a charm. Get all of the data together in a temp table first and then use something like this in a stored procedure to export the data:

SET @.EXPORTSQL=
'BCP "SELECT * FROM ##TEMPTABLE" QUERYOUT C:\FILE.TXT -c -t,'

EXEC MASTER..XP_CMDSHELL @.EXPORTSQL

This will export a comma separated values version of the temp table to a file. I have to upload mine to an FTP site, so I have a mapped drive on the server attached to that FTP site and then change the path to that mapped drive letter. The proc is then effectively creating the file and uploading in less than a second or two.

In my experience DTS is great, but there's not need to over complicate the product when a couple lines of SQL can get you there!