September 27, 2012 at 8:02 pm
I have a flat file with trailers that the total the amounts in certain columns. I need to import the trailer into a different table. How can I split the data so that I can put the trailer data into a different table. Here is the sample data below:
1002AMickey Mouse500.0025.00ACMECORP 1000.00
1002BDuck Donald 125.00 15.00 ACMECORP 2500.00
1002T Doe Jane 750.0020.00ACMECORP 2300.00
1002 1375.0060.00 5800.00 2010
10036740.5675.007800.23 2011
September 27, 2012 at 9:36 pm
apache626 (9/27/2012)
I have a flat file with trailers that the total the amounts in certain columns. I need to import the trailer into a different table. How can I split the data so that I can put the trailer data into a different table. Here is the sample data below:1002AMickey Mouse500.0025.00ACMECORP 1000.00
1002BDuck Donald 125.00 15.00 ACMECORP 2500.00
1002T Doe Jane 750.0020.00ACMECORP 2300.00
1002 1375.0060.00 5800.00 2010
10036740.5675.007800.23 2011
Try using conditional split.
However please provide more input for more assistance e.g.
1. What is the trailer part in above example ?
2. Is there any fixed pattern or length for this ?
3. Logic behind trailer generation in more detail.
--rhythmk
------------------------------------------------------------------
To post your question use below link
https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help
🙂
October 1, 2012 at 11:21 am
Agreed that the simplest way is to use a conditional split if possible. However, if your footer record(s) is not structured like your other rows, it's possible that you could get an error during processing, or worse, the footer row(s) would actually be processed with your data. In cases such as that, you may have to use a couple of derived column transformations to handle the data, or possibly a script component to programmatically filter out those rows.
This is one area where SQL Server 2012 offers a distinct advantage - it won't blow up if you have files with dissimilarly structured rows. If you're on 2012, the conditional split design pattern for filtering header/footer rows is usually your best bet.
hth,
Tim
Tim Mitchell, Microsoft Data Platform MVP
Data Warehouse and ETL Consultant
TimMitchell.net | @Tim_Mitchell | Tyleris.com
ETL Best Practices
October 1, 2012 at 12:32 pm
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply