Need to read one text file into three tables

[Phillip P]

Can the header record be read into one table, the detail into another and the trailer into a third within one data transformation or would this have to be done with an active script task.

I then have to take this data and write it back out to one file with a header and detail lines.

Would vb be better for that part or can it be done easily with active script using the file system object. I could write the one header record and append the detail lines after.

Any help would be appreciated.

Thanks,
DTS newbie.

[bill]

A single Transform Data task has only one source and one destination. However, I would use a Transform Data task, an ActiveX transformation (not copy column) within the task, and global variables to keep track of the current record number, and store everything in the detail table. For example, two lines would look like:

DTSGlobalVariables("NumRecords&#34.value = DTSGlobalVariables("NumRecords&#34.value + 1

DTSDestination("NumRecords&#34 = DTSGlobalVariables("NumRecords&#34.value

I would then add an Execute SQL task to
1) copy over record 1 to the header table
2) copy over record max(NumRecords) to the trailer table
3) delete record 1
4) delete record max(NumRecords)
5) zero out the NumRecord field in the detail table


To re-create the original file, I'd use an ActiveX script using VBscript and the "Scripting.FileSystemObject" to create a textfile and .write to it.



------------
Phillip P at 7/24/01 5:24:17 PM

Can the header record be read into one table, the detail into another and the trailer into a third within one data transformation or would this have to be done with an active script task.

I then have to take this data and write it back out to one file with a header and detail lines.

Would vb be better for that part or can it be done easily with active script using the file system object. I could write the one header record and append the detail lines after.

Any help would be appreciated.

Thanks,
DTS newbie.

[bill]

Regarding my solution, I agree with IVO that creating back the file should be done the way he mentions with a UNION query. I would still use DTS though, as he even mentions, but why fill a fourth table when most of the data will still end up in the detail table anyway. Why not fill the detail table with everything and then strip off the header and trailer lines...


------------
IVO at 7/26/01 1:58:59 AM

why to do this so complex ? Why don't you want to write one simple script using ADO to read a file and insert data into three tables, line by line, depending on a line number, or count or else simple criteria, that can say you, if the line is header, detail or trailer ?
Creating back a file is even more simple, because you can create one select command to join these three tables and to return header, detail and trailer as a columns of result, all converted to varchars or whatever you need. And ADO script would only write that three columns to one file.

Problem of this solution could be that it'll not run fast on huge files, because it is not using DTS. Better in this case could be loading whole file into one table and use insert into or select into commands to select data directly into target tables.


------------
bill at 7/25/01 4:16:26 PM

A single Transform Data task has only one source and one destination. However, I would use a Transform Data task, an ActiveX transformation (not copy column) within the task, and global variables to keep track of the current record number, and store everything in the detail table. For example, two lines would look like:

DTSGlobalVariables("NumRecords&#34.value = DTSGlobalVariables("NumRecords&#34.value + 1

DTSDestination("NumRecords&#34 = DTSGlobalVariables("NumRecords&#34.value

I would then add an Execute SQL task to
1) copy over record 1 to the header table
2) copy over record max(NumRecords) to the trailer table
3) delete record 1
4) delete record max(NumRecords)
5) zero out the NumRecord field in the detail table


To re-create the original file, I'd use an ActiveX script using VBscript and the "Scripting.FileSystemObject" to create a textfile and .write to it.



------------
Phillip P at 7/24/01 5:24:17 PM

Can the header record be read into one table, the detail into another and the trailer into a third within one data transformation or would this have to be done with an active script task.

I then have to take this data and write it back out to one file with a header and detail lines.

Would vb be better for that part or can it be done easily with active script using the file system object. I could write the one header record and append the detail lines after.

Any help would be appreciated.

Thanks,
DTS newbie.

[IVO]

yes, sure, only problem could be if structures of header, trailer and detail records was different. Our target is to fast load data from a file somewhere and fast dividing it into three tables. If a file contains 1000 lines, this discussion is a little bit funny )


------------
bill at 7/26/01 9:21:36 AM

Regarding my solution, I agree with IVO that creating back the file should be done the way he mentions with a UNION query. I would still use DTS though, as he even mentions, but why fill a fourth table when most of the data will still end up in the detail table anyway. Why not fill the detail table with everything and then strip off the header and trailer lines...


------------
IVO at 7/26/01 1:58:59 AM

why to do this so complex ? Why don't you want to write one simple script using ADO to read a file and insert data into three tables, line by line, depending on a line number, or count or else simple criteria, that can say you, if the line is header, detail or trailer ?
Creating back a file is even more simple, because you can create one select command to join these three tables and to return header, detail and trailer as a columns of result, all converted to varchars or whatever you need. And ADO script would only write that three columns to one file.

Problem of this solution could be that it'll not run fast on huge files, because it is not using DTS. Better in this case could be loading whole file into one table and use insert into or select into commands to select data directly into target tables.


------------
bill at 7/25/01 4:16:26 PM

A single Transform Data task has only one source and one destination. However, I would use a Transform Data task, an ActiveX transformation (not copy column) within the task, and global variables to keep track of the current record number, and store everything in the detail table. For example, two lines would look like:

DTSGlobalVariables("NumRecords&#34.value = DTSGlobalVariables("NumRecords&#34.value + 1

DTSDestination("NumRecords&#34 = DTSGlobalVariables("NumRecords&#34.value

I would then add an Execute SQL task to
1) copy over record 1 to the header table
2) copy over record max(NumRecords) to the trailer table
3) delete record 1
4) delete record max(NumRecords)
5) zero out the NumRecord field in the detail table


To re-create the original file, I'd use an ActiveX script using VBscript and the "Scripting.FileSystemObject" to create a textfile and .write to it.



------------
Phillip P at 7/24/01 5:24:17 PM

Can the header record be read into one table, the detail into another and the trailer into a third within one data transformation or would this have to be done with an active script task.

I then have to take this data and write it back out to one file with a header and detail lines.

Would vb be better for that part or can it be done easily with active script using the file system object. I could write the one header record and append the detail lines after.

Any help would be appreciated.

Thanks,
DTS newbie.