How to write 3 text files into 1 ?
-
I've just had the wonderful task of dropped on me of preparing a new file for a client of ours. Said client insists on a .dat file which is to contain 3 individual fixed width file specs per record. ie. header, items, address.
Any ideas on best approach to achieve this result and is it possible via a DTS? I could easily prepare the three files separately but I'm abit confused as to how to write them all to one file for multiple orders.
Many thanks,
HELP
-
I actually had to do this recently. Essentially i just created three separate text files and then concated them together using a simple copy command at the end of the package:
EXEC master.dbo.xp_cmdshell 'copy "c:\DTS\Talx Export\000record.tmp"+"c:\DTS\Talx Export\200record.tmp"+"c:\DTS\Talx Export\210record.tmp"+"c:\DTS\Talx Export\220record.tmp"+"c:\DTS\Talx Export\999record.tmp" "c:\DTS\Talx Export\11992003tstin.msf "'
This of course assumes that you don't need the 3 records for each customer do not need to be right after each other. In our case we could write all the header, all the detail type records and then all the footer type records.
If the sequence does matter and your in for some head scratching try the SQL Shaping Service. there is info on this in BOL. We tried this approach at first but it got really complicated and we had trouble with the performance and file format.
-
Thanks for that. Unfortunately I do have to have the sequencing right so looks like Shaping services for me then....
-
i'd suggest double checking the vendors requirement on this. I've found with two different vendors that the sequence didn't matter as long as the order of the record types come in correctly.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply