automated text file processing

Question

automated text file processing

mountcrumpit

Say Hey Kid

Points: 663
More actions
March 1, 2010 at 8:43 am

#143143

A little surprised I couldn't find something like this here.
Trying to routinize the processing of text files as they're created.
The DTS part of opening the text file, transforming it and processing it is straightforward.
I'm having trouble with the front and back ends. any links or help you folks can provide is greatly appreciated.
Front end. the DTS source is set as "somefile.txt". But while its processing -- another file may come in so, thinking, each file name coming in needs to be unique. Thirdparty software creates these files. The DTS is scheduled for every few minutes. But now the dts needs a (pre)step which would take the next file, rename it to "somefile.txt" and then continue. Any ideas? Can T-SQL/DTS interact with files and folders?
Back End. When complete I'd like to email the results (really just a bunch of "print" statements). I've seen email options for jobs and notifications but haven't seen anything on 'user' content.
Can I make SQL2005 do this?
thanks, b

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Ray Dai SSCommitted Points: 1548 More actions · Answer 1

Front end, can you not copy this file to a seperate folder? i.e create a "processing" folder under your current folder and move file there one at a time and process them so you don't have to deal with naming issues? You can do this in T-SQL via xp_cmdshell, see this link:

http://www.databasejournal.com/features/mssql/article.php/3372131/Using-xpcmdshell.htm

Email, you can use xp_sendmail. See here:

http://msdn.microsoft.com/en-us/library/ms189505.aspx

john.campbell-1020429 Ten Centuries Points: 1194 More actions · Answer 2

I agree with Ray. When faced with a job like this you can use a forfiles to process the file and in the last step for the file, move the processed file to another directory or rename to exclude from the next run of forfiles. I prefer code to dts but either will work well in this situation.

John.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

First, are the files formatted the same? If so, using a FOR EACH container could simplify the process along with dynamically configuring the connection to the import file. You may need a process to ensure that files that are still arriving aren't processed. PragmaticWorks as a community version (free) SSIS task named File Properties Task that can be used to verify that a file is not in use (being received for instance).

mountcrumpit Say Hey Kid Points: 663 More actions · Answer 4

Wow, thanks for the links/ pointers all. Been implementing other parts of this system and finally got back here. I haven't had a chance to digest or delve into this yet but wanted to thank you all immediately

mountcrumpit Say Hey Kid Points: 663 More actions · Answer 5

I found this:

http://blogs.msdn.com/luti/archive/2008/07/11/ssis-by-example-1-processing-files-in-folder.aspx

it seems to summarize everything into one process.

though having trouble with the user::filepath not providing a file name to the flat file connection when the foreach loop is in play (input file name:"")

note: this is for 2008, i've been attempting on 2005. see my other post today re strangeness there and this effort