Export to Text File with Header and Footer records

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

Export to Text File with Header and Footer records

Author

Topic

gchacko
Starting Member

8 Posts

Posted - 2002-08-23 : 17:17:58

I haven't seen a question posted on this issue.
Problem:
1.Write Header record to a text file (Company Name,Date Created)
2.Select records based on the SQL query.
3.Write rows to a file.
4. Write Footer record to text file (Total records processed)

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2002-08-23 : 19:09:51

Well you could use some technique like xp_cmdshell (if you have permissions to it) to write out the header and footer, and use BCP to write out the data, and then use command-line commands to concatenate them all together into one file.

jasper_smith
SQL Server MVP & SQLTeam MVY

846 Posts

Posted - 2002-08-23 : 19:10:51

I tend to use bcp too. Just dump everything into a temp table and bcp out of it. However if you want to use DTS here's an example to give you an idea.
Create a package with a SQL connection a Text (Destination) connection and a Transform specifying the data to export. THen create an ActiveX Script Task than runs after the transform completes (use the On Success Workflow). In the ActiveX Script paste the following. This example uses hard code values but you could use Global Variables, a table lookup, an ini file etc. To get your row count you could use an execute SQL task instead and put the rowcount in a global variable accessible by the script.


Function Main()
	
	   Dim fso,WshShell,h,hline,t,tline,hpath,tpath,shCmd,final
	   Dim dConn,dfile,rcount

	   '************************************
	   ' Get Destination Text file Name
	   '************************************
	   Set dConn = DTSGlobalVariables.Parent.Connections("Text File (Destination)")
           dfile = dConn.DataSource

	   '************************************
	   ' Get Rowcount (for example)
	   '************************************
           rcount =  DTSGlobalVariables("count").value

	   '****************************************************************
	   ' These are hard coded but could easily be gotten from
	   ' Global Variables,table lookups,ini file etc
	   ' THe header and trailer rows could be from a SQL query
 	   '*******************************************************************
	   hline = "HDR" + CStr(FormatDateTime(Date, 2)) + "001"
	   tline = "TRAILER+++++++++" & rcount
	   hpath = "c:\header.txt"
	   tpath = "c:\trailer.txt"
	   final = "c:\dts.txt"
	
	   '*******************************
	   ' This cmd merges the files
	   '*******************************
	   shCmd = "%comspec% /c copy /B/Y " & hpath &_
		   "+" & dfile & "+" & tpath & " " & final
	
	   Set fso = CreateObject("Scripting.FileSystemObject")
	   set WshShell = CreateObject("WScript.Shell")
	 
	   '*******************************
	   ' Write the header record
	   '*******************************	  
	   Set f = fso.OpenTextFile(hpath, 2, True)
	   f.WriteLine hline
	   f.Close
	
	   '*******************************
	   ' Write the trailer record
	   '*******************************	
	   Set t = fso.OpenTextFile(tpath, 2, True)
	   t.WriteLine tline
	   t.Close
	
	   '*******************************
	   ' Merge the files
	   '*******************************	
	   WshShell.run shCmd,0,True
	
	   Set fso = Nothing
	   Set WshShell = Nothing

	Main = DTSTaskExecResult_Success

End Function

HTH
Jasper Smith

Edited by - jasper_smith on 08/23/2002 19:19:46

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources