I have DTS package, which loads data from SQL Stored Proc into text file via
Transformation task.
How can I insert title as a first record in the text file?
Thanks,
Ilin

--
Message posted via http://www.sqlmonster.com

The way 99% of people use is to export the title to a different file and
unite the data text file with the header file using COPY on the cmdline



--



Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.SQLIS.com - You thought DTS was good. here we show you the new stuff.
www.konesans.com - Consultancy from the people who know


"Ilin Start via SQLMonster.com" <forum (AT) SQLMonster (DOT) com> wrote

Thanks, but unfortunately I can not do this. It should be done inside of data
transformation task and should be run automatically on scheduler.
Any other idea?
Ilin

--
Message posted via http://www.sqlmonster.com

Ilin, I have done a similar thing that you want to do in a transformation
task for Excel files. As I understand it, you want to automatically insert a
title line at the beginning of the text file in the data transformation task.
First, one might change the data types of all the columns in your stored
procedure result set to varchar(nnn) using the convert function. Then one
might modify the stored procedure so that the first row in the result set it
returns contains your desired title mapped to the fields in your text file
using UNION and appropriate sort orders in your stored procedure. The
results may not look picture perfect.

"Ilin Start via SQLMonster.com" wrote:

Ilin, Your earlier post did not mention whether the first row of your
exported text file has column names. In the DTS text file connection
properties , one can specify whether the first row has column names. The
method which I described in the earlier post assumes the first row has no
column names. If your text file output has column headers , then Allan
Mitchell's post is your answer. Thank you.

"Ilin Start via SQLMonster.com" wrote:

Ilin, If the first row of your text file has column names, then you can still
implement Allan Mitchell's suggestion in an Active Script task at the finish
of your DTS workflow by appending the data file to the title file. This
workflow can still run automatically on the scheduler. The following code is
similar to an Active X script I just tested.

Const ForAppending = 8
Dim sFileA
Dim sFileB
Dim sFileC
Dim oFS

sFileA = "C:\file1.txt"
sFileB = "C:\file2.txt"
sFileC = "C:\file3.txt"
set oFS = CreateObject("Scripting.FileSystemObject")
oFS.CopyFile sFileA, sFileC
oFS.OpenTextFile(sFileC, ForAppending)_
.Write oFS.OpenTextFile(sFileB).ReadAll

'It is limited to files that will fit within available memory, but that
should be several megabytes in nearly all instances.

"Ilin Start via SQLMonster.com" wrote:

Thank you very much for all your postings. My first row in a text file has
column names. I already tried to include in stored proc additional column
with report title. But this solution is not good since it appears in each
record of text file. I will try to append files as per your advice.
Also I need to make text file on shared drive read only but be able to run
DTS on scheduler automatically (write this file). Any suggestion how I can
make this file read only but be able to write this file from DTS?
Thanks,
Ilin

--
Message posted via http://www.sqlmonster.com

I forgot to mention that title of the file should include information
(variables) from my stored proc (date and time). Should I create a separate
stored proc, which will send to the file with title just title line including
variables and then append to it my second file with result set? Do you know
the better way of doing this (hopefully in one shot)? Another question: can I
format file with data in ActiveX script (or somehow) to present a good
picture of the report? I used delimited formatting of text file with tab in
my transformation task.
Thanks,
Ilin

--
Message posted via http://www.sqlmonster.com

Ilin, Here is one way to make the DTS-generated text file output read-only.
In the final Active X script after the Data Transformation Task, use
something like:

Function Main()
Const READ_ONLY = 1
Const ForAppending = 8
Dim sFileA
Dim sFileB
Dim sFileC
Dim oFS
Dim objFile


sFileA = "C:\file1.txt"
sFileB = DTSGlobalVariables("FileName").Value
sFileC = "C:\file3.txt"
set oFS = CreateObject("Scripting.FileSystemObject")
oFS.CopyFile sFileA, sFileC
oFS.OpenTextFile(sFileC, ForAppending)_
.Write oFS.OpenTextFile(sFileB).ReadAll

Set objFile = oFS.GetFile("C:\file3.txt")

' Ilin, The following code makes the DTS output text file read-only
If Not objFile.Attributes AND READ_ONLY Then
objFile.Attributes = objFile.Attributes XOR READ_ONLY
End If

Main = DTSTaskExecResult_Success
End Function

Thank you.

"Ilin Start via SQLMonster.com" wrote:

Thank you very much, it is working. One more question: Is it possible to
restrict permissions on the final file in DTS to read only (not just
attribute because attribute can be easily changed by the user) and if yes how
to do it inside of ActiveX script?

Thanks again,
Ilin

--
Message posted via http://www.sqlmonster.com