October 5, 2010 at 2:25 am
I need to read text file that essentially contains three types of records, each of the same total length but different numbers of fields. Each record type has diffrent fields which all need to be put back into a text file ready for further processing by another system.
The first row is the file header, the rest of the records are group headers followed by detail records belonging to the group header.
What would be the best way of approaching this? I have looked at a Looping / Script File Task and appending the records into pre-defined SQL tables, but the fact that I need to read 3 different types of records (identified by a record type field) from 1 text file throws me a bit. I feel I need to break the file into 3 types (or groups) of records and then union it back together.
Any ideas greatly appreciated.
Regards
Steve
October 5, 2010 at 2:58 am
One possible solution is to read the complete line in the file as a single column using flat file connection manager & flat file source. Use a script component to identify the record type using the row value and set the record type identifier to a new output column.
Then split the records using conditional split based on the record identifier for further processing.
Another script component for each record type would be required to extract the column values and set it as output column.
Regards,
Suresh
October 5, 2010 at 3:25 am
I agree up to the point of importing as a single column and firing into a Script Component.
From there, I would do things differently from what is suggested: a Script Component with multiple outputs would seem ideal. See here for an explanation and example.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
October 5, 2010 at 3:42 am
Yes, single script component with multiple outputs will do... instead of a conditional split task and a script for each record type.
October 5, 2010 at 8:57 am
Thanks for all your replies. I'm still workingh on the solution but am nearly there (using Conditional Split)
October 5, 2010 at 9:10 am
One more question on this...
As I split the records up from the original file, the detail records need to have some information from their header records appended to them. The only way of knowing which detal records belong to which header records is the order in the file, such a rec type of 3 will be followed by one or more rec type 4's. When the rec type changes back to a 3 i know I'm onto a new customer.
How can I append the group header info to the detail record if i am using a Conditional Split. Variables in some way?
Steve
October 5, 2010 at 9:31 am
Steve Hindle-535638 (10/5/2010)
One more question on this...As I split the records up from the original file, the detail records need to have some information from their header records appended to them. The only way of knowing which detal records belong to which header records is the order in the file, such a rec type of 3 will be followed by one or more rec type 4's. When the rec type changes back to a 3 i know I'm onto a new customer.
How can I append the group header info to the detail record if i am using a Conditional Split. Variables in some way?
Steve
The only way I've been able to do that (assuming I understood correctly) is by using a VB transform before the conditional split. The transform as added columns for the values that need to be held for use by upcoming rows.
In the VB script I create a "global" variable for each column that I need to hold the value. The logic goes along the lines of if the row is of the correct type, assign the value to the variable, else do nothing. Write this/these variable(s) to the output with every row that is read.
I tried numerous other methods and they all failed.
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
October 5, 2010 at 9:42 am
That sounds like just what i need.
When you say VB Transform what exactly do you mean? Do you have an example of the code? My data looks like:
3000000
4111111
4111111
4111111
3000111
4111222
4111222
4111222
So 3000000 goes with 4111111, 3000111 goes with 4111222 etc...
Many thanks
Steve
October 5, 2010 at 9:49 am
Alvin Ramard (10/5/2010)
Steve Hindle-535638 (10/5/2010)
One more question on this...As I split the records up from the original file, the detail records need to have some information from their header records appended to them. The only way of knowing which detal records belong to which header records is the order in the file, such a rec type of 3 will be followed by one or more rec type 4's. When the rec type changes back to a 3 i know I'm onto a new customer.
How can I append the group header info to the detail record if i am using a Conditional Split. Variables in some way?
Steve
The only way I've been able to do that (assuming I understood correctly) is by using a VB transform before the conditional split. The transform as added columns for the values that need to be held for use by upcoming rows.
In the VB script I create a "global" variable for each column that I need to hold the value. The logic goes along the lines of if the row is of the correct type, assign the value to the variable, else do nothing. Write this/these variable(s) to the output with every row that is read.
I tried numerous other methods and they all failed.
Alvin
Did you try just holding the 'current' header value in an appropriately scoped variable within a Script Component?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
October 5, 2010 at 9:51 am
Steve Hindle-535638 (10/5/2010)
That sounds like just what i need.When you say VB Transform what exactly do you mean? Do you have an example of the code? My data looks like:
3000000
4111111
4111111
4111111
3000111
4111222
4111222
4111222
So 3000000 goes with 4111111, 3000111 goes with 4111222 etc...
Many thanks
Steve
I'm using a Script Component in the Data Flow. When adding a Script Component to a Data Flow you have to select the component type. Select Transformation.
I'm sorry but this is something I did for a user group presentation a while back and I do not have the code handy.
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
October 5, 2010 at 9:55 am
Phil Parkin (10/5/2010)
AlvinDid you try just holding the 'current' header value in an appropriately scoped variable within a Script Component?
Phil, I'm not sure what you're asking? ...appropriately scoped variable within a Script Component?
I used a variable that I declare outside of the "Main" subroutine.
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
October 5, 2010 at 10:00 am
Alvin Ramard (10/5/2010)
Phil Parkin (10/5/2010)
AlvinDid you try just holding the 'current' header value in an appropriately scoped variable within a Script Component?
Phil, I'm not sure what you're asking? ...appropriately scoped variable within a Script Component?
I used a variable that I declare outside of the "Main" subroutine.
Exactly that! Made sense to me and that's how I would have done it 🙂
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
October 5, 2010 at 10:04 am
Phil Parkin (10/5/2010)
Alvin Ramard (10/5/2010)
Phil Parkin (10/5/2010)
AlvinDid you try just holding the 'current' header value in an appropriately scoped variable within a Script Component?
Phil, I'm not sure what you're asking? ...appropriately scoped variable within a Script Component?
I used a variable that I declare outside of the "Main" subroutine.
Exactly that! Made sense to me and that's how I would have done it 🙂
I need to go back and check if it made a difference whether it was declare inside or outside of the Main sub. I was thinking that if I declared it inside Main the value might be reset every row so I tried it outside of Main. That worked so I left it as is.
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply