• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 477
  • Last Modified:

Need to add an EOF marker to a Tab delimted text file being exported from SQL using DTS

I am VERY new to SQL and DTS.  I have created a SQL statement that selects the data I need and I have figured out how to use the DTS Export wizard to create a tab delimited file.  However, I need to add an EOF marker to the end of the destination text file.  I also need to name the destinatiion file dynamically based on the date.  Can you help me with some very detailed instructions on how to do this?

Thanks, DixieBP
0
dixiebp
Asked:
dixiebp
  • 3
  • 2
2 Solutions
 
Anthony PerkinsCommented:
>>However, I need to add an EOF marker to the end of the destination text file. <<
Any reason why? And if so define EOF in ASCII

>>I also need to name the destinatiion file dynamically based on the date. <<
See here:
How can I change the filename for a text file connection?
http://www.sqldts.com/200.aspx
0
 
dixiebpAuthor Commented:
Thank you.....the file naming now works.  Do you know of a way too add an EOF marker after the last line of data in the text file?
0
 
Anthony PerkinsCommented:
>>Do you know of a way too add an EOF marker after the last line of data in the text file?<<
Again, any reason you have to do that?  An EOF character is very rarely needed and has not been needed since mid-80's. If you still want to do it, than you will have to define what you mean by EOF in ASCII.  CHAR(26) perhaps?
0
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
dixiebpAuthor Commented:
We are sending the file to a company that uploads it into their system.  They require some type of EOF marker to they know they have received a complete file.  It can literally be the text "EOF" after the last line of data in the file.
0
 
Anthony PerkinsCommented:
Than you will have a couple of options:
1. Open the file using the FileSystemObject and write it out, when you get to the end write out "EOF".
2. Change your query that exports the data to a UNION query and add SELECT "EOF" at the end.  This second option is simpler, but may be dependent on the number of columns and column delimiter.
0
 
Mark WillsTopic AdvisorCommented:
Or even at the end of your export, run a command or batch that simply does a copy / concatenate

at the dos prompt, try this :

1) as a one time process, create an EOF file:
echo EOF > eof.txt

2) then as a normal part of your process, run a command / batch to:

copy myexportedtable.txt+eof.txt newexportedfilewitheof.txt

0

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now