sql server dts: i have to export customer sales data into a text file.

the file name has to be the customer ID ==> 123456.txt
the first line has to be the customer ID followed by the
sales data.

123456
415.25
666.90
-25.32
996.21 and so on...

can i use one of the DTS tools - not the wizard - it has
to be done on a schedule.

thanks,
lynn.

Setting the filename would be done using a variation of

How can I change the filename for a text file connection?
(http://www.sqldts.com/default.aspx?200)

I would go with an export to two text files

1. Has the CustomerID and
2. The other has the sames data

You marry them up at the end using

COPY Header.txt + Data.txt MyFile.txt

You can do this using the Execute Process task and manipulating it

How to manipulate the Execute Process task.
(http://www.sqldts.com/default.aspx?251)

Getting the sales data could be done using a loop through a rowset

How to loop through a global variable Rowset
(http://www.sqldts.com/default.aspx?298)


Why do you need the CustomerID inside the file if you have it as part of the
filename?
You could simply parse the filename at the other end if you needed it.

Scheduling is done through SQL Server Agent so no problems there.

--

----------------------------

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


[quoted text, click to view]