DTS or Script?

[melbert2002]

I have an assignment...I have a Database where nightly we have one of our vendors processes the database and spits out a TEXT file. We are going to change the way we are doing things at my office so that we will be processing the database and spitting out the Text file for others to use.
Here is my question: I will have about 20 different types of Text files that need to be processed. Of those 20 different Text files, each one will have between 3 and 50 different customers that are customized for the individual although they each have the exact same fields, just customized data. Oh and to top it off, each txt file needs to be incremental.

Should I use a script and put it as a Job? Use DTS? is there a way I can process each querry using the same DTS job, just automaticly spit out the 50 individual txt files for each customer?

Thanks in advance.

 

[nigelrivett]

I would use bcp.
How many queries do you have to produce the data for the files?
How many file formats do you have?
There is a choice between a different query or bcp statement for each file but that will be labou rintensive and difficult to maintain.
Better to create the queries (or views) to create the data then hold the file format for each file in a table

filename, colid, fieldname, separator, format
then use the table to build the bcp statement.
Then you only have one sp to do all the extracts and a new extract is just a few entries in a table.

I have an example of a similar thing for imports here:

http://www.nigelrivett.net/MoveImportData.html

I didn't post the export version because it's so much simpler.


======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[melbert2002]

Thanks,
The only format for the files are simple text files. Now whether they are going to be csv or tab diliminated I am not sure yet.

Sorry but I am a little new to MS SQL, but if I understand you on this.....

Basicly I should create a querry and store the result as a view in a table, then just export that view to a text file? That is an excellent idea. I am not that conserned about the labor of the machine. The DB is not that big (3GB) and the machine that I will process it on is pretty good (Dual 3GHZ processor 4 GB of Ram).

The only thing I am conserned about is managing the querries and making sure that they are incremental. My plan is to do this at about 1AM so should I just process the previous day's new entries? Or is there a way SQL can know what happened to the results of the querry previously?

Thanks
mel

 

[nigelrivett]

Depends where you are getting the data from. If there is an increasing field then use that.
Create a table to store this field and

declare @maxfld ...
declare @minfld....
select @minfld = fld from savfldtbl where tbl = 'mytbl'
select @maxfld = max(fld) from mytbl

export from mytbl where fld > @minfld and fld <= @maxfld

update savfldtbl set fld = @maxfld where tbl = 'mytbl'

It's important to get the max value and use it in the query in case records are added while the export is going on.

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[yksvaan]

There's a lot of issues in your project, and I am not able to give you answers. I think you should write down a detailed plan of the system including directory and filenames etc. and discuss it with other professionals which tools to choose.

However, if you need to produce multiple textfiles with the exactly same field structure from a query, DTS (sql2k) has a nice feature just for this called Write File Transformation.

My main message though is that DTS/SQL won't do the job of automatically auditing which rows have been already transfered. Some software have better and some nearly none building blocks for this specific issue, but it is always dependent on nature of data and update-cycles and so on. You have to desing the "mechanism" yourself and implement it with whatever language, is it sql-clauses or something else.

Cheers