Hi,

I need to export some data to a text file from sql server. I'm using Sql DTS
to do this. I also need to have a header in the text file followed by the
data exported from the sql database. I created a text file with header then
used dts to export but the header gets deleted when the dts exports the
file...so is there anyway to append the datas exported by sql dts to the text
file.

Thanks and Regards,
Karunakaran

There are a few ways you could do this. You can create two
files: a header file and the data file. Then you can combine
the files into the final file using the DOS copy command
with something like:
COPY Header.txt + Data.Txt
You can also combine the two files using ActiveX script and
FileSystemObject.
You could use a SQL statement that unions the header, and
data info
You could also use the Multiphase Data Pump functionality to
create header and/or trailers for files.
The first option is often easier to do.

-Sue

On Tue, 29 Mar 2005 03:31:04 -0800, "Karunakaran"
<Karunakaran (AT) discussions (DOT) microsoft.com> wrote:

Thanks for the input. It did worked. I have posted the data flow in
http://asia.geocities.com/vij_karan/dtsflow.jpg

Let me know if there are any suggestions. Any pointers towards Multiphase
Datapump would be appreciated.

Karunakaran

"Sue Hoegemeier" wrote:

Hi,

The DTS package works fine for the first time and subsequent runs when the
connection is not closed. When I close the Enterprise Manager and execute the
package again later everthing works fine till the batch process during the
batch process the files are not copied to one file. Any ideas what could be
the problem.

Thanks and Regards,
Karunakaran

"Karunakaran" wrote:

Not really enough information to make a guess. What do you
mean by it runs when the connection is not closed?

-Sue

On Thu, 31 Mar 2005 23:55:03 -0800, "Karunakaran"
<Karunakaran (AT) discussions (DOT) microsoft.com> wrote:

Hello Sue,

When I mean the connection is not closed, I mean by not closing the
enterprise manager. Well now whether irrespective of whether I close the
enterprise manager or not still I could able to get this work. Sometimes the
batch does what its supposed to do sometimes the final file is still blank
nothing gets written to it.

Many posts says the easier way to achieve header and footer is to use
multiphase data pump but I dont find any code snippets which does it.

Any pointers towards it?

Thanks and Regards,
Karunakaran

"Sue Hoegemeier" wrote:

Possibly how you are calling the batch file. The Win32
Process should be something like:
Win 32 Process: cmd.exe
Parameters: /c copy D:\Header.txt+D:\Data.txt
D:\FinalFile.txt
Enable the multiphase data pump, write the header in the Pre
Source Data phase. You can find some notes on it's usage at:
http://www.sqldts.com/default.aspx?t...&i=282&p=1&a=7

-Sue

On Mon, 4 Apr 2005 02:07:03 -0700, "Karunakaran"
<Karunakaran (AT) discussions (DOT) microsoft.com> wrote:

The batch file(sqlbatch.bat) has the following
copy/b D:\Header.txt+D:\Data.txt+ D:\FinalFile.txt

Win32 Process
I just call the batch file sqlbatch.bat

Other one from other forum suggested that I use the following
Type Header.txt>FinalFile.txt
Type Data.txt>>FinalFile.txt
Type Footer.txt>>FinalFile.txt

This seems to working..Atleast for now...Still I couldnt able to figure out
whats wrong with the batch file.

Thanks and Regards,
Karunakaran



"Sue Hoegemeier" wrote:

Another problem you may encounter is when you exit and reopen EM, you
lose the path for the current working directory of the batch file, so
you need to specify where you want the batch file to copy from.
use the cd command to switch to the directory where your files are located.
IE -
REM Begginng of batch file
C:
cd Temp --You are now in the temp directory on your C: drive

Simon Worth

Karunakaran wrote:

Thanks Simon.
That does work.

Karunakaran

"Simon Worth" wrote: