February 4, 2002 at 7:58 am
Hi, All
I have a situation here.. I need help..
Situation is I need to create Text file that I need to send out to vender for processing info.
I use DTS to pull data from one server to the other. I can pull data and create text file using DTS but how can I put header & Trailer info after or before I pull the data? Is there any DTS function or I need to do something else???
Thx in advance
Jay
February 4, 2002 at 8:15 am
You could use the filesystem object (scripting host). This would be instanciated in an activeX script in a dts package, and would create a new file (FILEA), write out the header, read the datafile (FILEB) and then append the data to FILEA.
Paul Ibison
Paul Ibison
Paul.Ibison@replicationanswers.com
February 4, 2002 at 10:51 am
I like Pauls suggestion.
Steve Jones
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
February 4, 2002 at 11:01 am
Thx.. All..
Let me try... What about Trailer info..I think that goes to the same way with header info..:-))
Is there any info that I can reference with creatig Text File?
Jay
February 4, 2002 at 11:29 am
You can look at my PushFTP article for sample text file creation. Also here's the ref from MS:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/script56/html/vtoriobjects.asp
Steve Jones
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
February 5, 2002 at 2:43 pm
I had the same need as you in that I had to put some data into a text file with a two line header of information. My final step was to select * from a view and put it into the text file so I just used a data transformation task based on the following type of query:
SELECT 'Whatever the first header should say'
UNION
SELECT 'Whatever the second header should say'
UNION ALL
SELECT * FROM
Pretty simple but it got the job done. You could even add a footer info as well.
Alan
February 7, 2002 at 1:34 pm
I tried that but it said there is no destination column.. When I take a look at the destination, it doesnt show up the all columns..What did I do wrong?
Jay
Edited by - rheejay on 02/07/2002 1:40:00 PM
February 8, 2002 at 12:38 am
quote:
I tried that but it said there is no destination column.. When I take a look at the destination, it doesnt show up the all columns..What did I do wrong?Jay
Edited by - rheejay on 02/07/2002 1:40:00 PM
February 8, 2002 at 12:47 am
Woops. Sorry. I forgot to add that you need to add an alias to the first select statement in order to create a column for the DTS task to use. For example try running the following query in a DTS package on the Northwind database and output to a text file:
SELECT 'Header line one' AS Col1
UNION ALL
SELECT 'Header line two'
UNION ALL
SELECT 'Header line three'
UNION ALL
SELECT CustomerID
FROM Customers
UNION ALL
SELECT 'Footer line 1'
You should use UNION ALL statements because the header order could be random with just UNION.
Hope this helps.
Alan
February 12, 2002 at 7:35 am
Thx for all..
Here is my 2 cents to help somebody who has a similar problem like me..
Actually I needed a little bit of details to solve my problem.. Many contributors help to guide my way of thinking:-))). However for somebody like me, I add this script..
Hope this will help..
Jay
P.S: I copy and paste into this box so lines are not correctly stick to each other.. Therfore when U copy & paste into Active X script, Please be careful:-)))
/*******************************************/
Function Main()
Const ForReading=1, ForWriting=2, ForAppending=8
Dim fso, f, _ ReadDataFile,AppendToHeader,FinalFile
Dim dMonth, dDay, dTime,dMin
Set fso = CreateObject ("Scripting.FileSystemObject")
'Create Text File & Insert Header Info
Set f = fso.CreateTextFile ("c:\testfile.txt", True)
Select Case Len(Month(Date))
Case "1"
dMonth = "0" & Month(Date)
Case Else
dMonth = Month(Date)
End Select
Select Case Len(Day(Date()))
Case "1"
dDay = "0" & Day(date())
Case Else
dDay = Day(date())
End Select
Select Case Right(Time(), 2)
Case "AM"
dTime = "0" & Left(Time(), 1)
Case Else
dTime = Left(Time(), 1) + 12
End Select
Select Case Len(Replace(Time(), ":", ""))
Case "8"
dMin = Mid(Replace(Time(), ":", ""), 2, 4)
Case "9"
dMin = Mid(Replace(Time(), ":", ""), 3, 4)
End Select
f.WriteLine("HEADER" & YEAR(DATE( )) & dMonth & dDay & dTime & dMin)
f.Close
'Open Data File to read
Set fso=CreateObject("Scripting.FileSystemObject")
Set f = fso.OpenTextFile("C:\xop1105.txt", ForReading)
ReadDataFile=f.ReadAll
'MsgBox ReadDataFile
'Open Header text file to append Data file which is already created before
Set f = fso.OpenTextFile("C:\testfile.txt", ForAppending)
AppendToHeader=f.Write(ReadDataFile)
f.Close
'Open text file(Header+Data) to insert Trailer info
Set f = fso.OpenTextFile("C:\testfile.txt", ForAppending)
FinalFile=f.Writeline("TRAILER")
f.Close
Set fso=Nothing
Set f=Nothing
Main = DTSTaskExecResult_Success
End Function
/******************************************/
Edited by - rheejay on 02/12/2002 07:38:18 AM
December 5, 2002 at 2:26 pm
I have the same need - to add a header and footer to a flat file. Microsoft SQL Help says that in the Transformation Pre source Phase you can add a header and in the Post Source Phase you can add a footer. I can't seem to find any examples of how to do this. How would I add the header and footer using this method?
Thanks
Mark
December 5, 2002 at 3:04 pm
quote:
Hi, Allhow can I put header & Trailer info after or before I pull the data? Is there any DTS function or I need to do something else???
Here's a low-brow solution:
Create a DOS bat file that copies your header/trailer info to your existing file
copy header.txt + datafile.txt + trailer.txt datafile.txt
Is this too easy?
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply