question

I assume that this question follows [the one asked yesterday][1], which was eventually resolved with the query returning data from 2 tables as one CRLF delimited string. I suppose that this string is then written to the destination flat file. If you would like to continue with this route, but add some extra info to the file (header line on the top and the trailer line on the bottom which will display the number of records) then you can just add one small select for each and **union all** the results. Here is the sample based on the query in yesterday's comments (I removed majority of the columns from there to keep my answer shorter in size): select '_Header Row and SomeHeaderInfo' union all select stuff(replace( ( select distinct char(10) + convert(varchar(1), RecordType) + convert(varchar(3),BrokerDealNumber) + convert(varchar(3),BranchNumber) + convert(varchar(9),PershAcctNumber)+ (select top 1 convert(varchar(6), TransactionBookKeepingDate, 112) from dbo.PershExtractTableA aa where atab.PershAcctNumber = aa.PershAcctNumber) + ( select char(10) + convert(varchar(1), RecordType)+ convert(varchar(3), BrokerDealNumber)+ convert(varchar(3), BranchNumber) + convert(varchar(9), PershAcctNumber) + convert(varchar(1), PershingAccountType) from dbo.PershingExtractTableT ttab where ttab.PershAcctNumber = atab.PershAcctNumber for xml path('') ) from dbo.PershExtractTableA atab for xml path('')), char(10), char(13) + char(10)), 1, 2, '') union all select '_Trailer Row: ' + convert(varchar(10), (select count(1) from dbo.PershExtractTableA) + (select count(1) from dbo.PershingExtractTableT)); However, I would like to point out that it is not really necessary to use for xml to concatenate the records like this if SSIS is used to actually create and populate the file. If you have a task pumping data from your 2 tables to the destination flat file and the Row Delimiter property has already been configured to be {CR}{LF} then you should simply return the **union all** of your selects: - header - records from dbo.PershExtractTableA - records from PershingExtractTableT - trailer info (sum of both counts) and let SSIS task to take care of adding your records to the file. The only small issue that you need to overcome is the correct sorting of your results. Here is the sample query which is constructed in the way to guarantee that your file will consist of the header on first line, parent and child tables records arranged such that the parent record is followed by all its child records followed by next parent etc, followed by the trailer record displaying the total count of the records in the file (excluding the count of header and trailer lines of course): ;with records(Order1, Order2, Data) as ( select 0 Order1, 0 Order2, '_Header Row and SomeHeaderInfo' Data union all select distinct row_number() over (order by PershAcctNumber), 0, convert(varchar(1), RecordType) + convert(varchar(3),BrokerDealNumber) + convert(varchar(3),BranchNumber) + convert(varchar(9),PershAcctNumber)+ (select top 1 convert(varchar(6), TransactionBookKeepingDate, 112) from dbo.PershExtractTableA aa where atab.PershAcctNumber = aa.PershAcctNumber) from dbo.PershExtractTableA atab union all select dense_rank() over (order by PershAcctNumber), 1, convert(varchar(1), RecordType)+ convert(varchar(3), BrokerDealNumber)+ convert(varchar(3), BranchNumber) + convert(varchar(9), PershAcctNumber) + convert(varchar(1), PershingAccountType) from dbo.PershingExtractTableT ttab union all select 2147483647, 0, '_Trailer Row: ' + convert(varchar(10), (select count(1) from dbo.PershExtractTableA) + (select count(1) from dbo.PershingExtractTableT)) ) select Data from records order by Order1, Order2; You can use any of the queries above, but I really believe that the second one is more suitable for SSIS solution while the first one is good enough for a small result set which is easy enough to copy from the query results window by hand (assuming results to text option). Oleg [1]: http://ask.sqlservercentral.com/questions/34755/output-file-formatting-in-ssis